Socket
Socket
Sign inDemoInstall

sql

Package Overview
Dependencies
Maintainers
1
Versions
101
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

sql - npm Package Compare versions

Comparing version 0.23.0 to 0.24.0

lib/dialect/index.js

21

lib/dialect/mysql.js

@@ -21,17 +21,17 @@ 'use strict';

Mysql.prototype.visitParameter = function(parameter) {
this.params.push(parameter.value());
return "?";
/* jshint unused: false */
Mysql.prototype._getParameterPlaceholder = function(index, value) {
return '?';
};
Mysql.prototype.visitCreate = function(create) {
var result = Mysql.super_.prototype.visitCreate.call(this, create);
var engine = this._queryNode.table._initialConfig.engine;
var result = Mysql.super_.prototype.visitCreate.call(this, create);
var engine = this._queryNode.table._initialConfig.engine;
var charset = this._queryNode.table._initialConfig.charset;
if (!!engine) {
if ( !! engine) {
result.push('ENGINE=' + engine);
}
if (!!charset) {
if ( !! charset) {
result.push('DEFAULT CHARSET=' + charset);

@@ -43,7 +43,2 @@ }

/*jshint unused: false */
Mysql.prototype.visitDefault = function(parameter) {
return "DEFAULT";
};
Mysql.prototype.visitRenameColumn = function(renameColumn) {

@@ -53,3 +48,3 @@ var dataType = renameColumn.nodes[1].dataType || renameColumn.nodes[0].dataType;

' (CHANGE COLUMN statements require a dataType)');
return ['CHANGE COLUMN ' + this.visit(renameColumn.nodes[0]) + ' ' + this.visit(renameColumn.nodes[1])+' '+dataType];
return ['CHANGE COLUMN ' + this.visit(renameColumn.nodes[0]) + ' ' + this.visit(renameColumn.nodes[1]) + ' ' + dataType];
};

@@ -56,0 +51,0 @@

111

lib/dialect/postgres.js
'use strict';
var _ = require('lodash');
var assert = require('assert');
var From = require(__dirname + '/../node/from');
var Select = require(__dirname + '/../node/select');
var Table = require(__dirname + '/../table');
var From = require('../node/from');
var Select = require('../node/select');
var Table = require('../table');
var Postgres = function() {

@@ -16,2 +18,45 @@ this.output = [];

Postgres.prototype._getParameterText = function(index, value) {
if (this._disableParameterPlaceholders) {
// do not use placeholder
return this._getParameterValue(value);
} else {
// use placeholder
return this._getParameterPlaceholder(index, value);
}
};
Postgres.prototype._getParameterValue = function(value) {
// handle primitives
if (null === value) {
value = 'NULL';
} else if ('boolean' === typeof value) {
value = value ? 'TRUE' : 'FALSE';
} else if ('number' === typeof value) {
// number is just number
value = value;
} else if ('string' === typeof value) {
// string uses single quote
value = this.quote(value, "'");
} else if ('object' === typeof value) {
if (_.isArray(value)) {
// convert each element of the array
value = _.map(value, this._getParameterValue, this);
value = '(' + value.join(', ') + ')';
} else {
// rich object represent with string
value = this._getParameterValue(value.toString());
}
} else {
throw new Error('Unable to use ' + value + ' in query');
}
// value has been converted at this point
return value;
};
Postgres.prototype._getParameterPlaceholder = function(index, value) {
return '$' + index;
};
Postgres.prototype.getQuery = function(queryNode) {

@@ -23,5 +68,27 @@ // passed in a table, not a query

this.output = this.visit(queryNode);
return { text: this.output.join(' '), values: this.params };
// create the query object
var query = { text: this.output.join(' '), values: this.params };
// reset the internal state of this builder
this.output = [];
this.params = [];
return query;
};
Postgres.prototype.getString = function(queryNode) {
// switch off parameter placeholders
var previousFlagStatus = this._disableParameterPlaceholders;
this._disableParameterPlaceholders = true;
var query;
try {
// use the same code path for query building
query = this.getQuery(queryNode);
} finally {
// always restore the flag afterwards
this._disableParameterPlaceholders = previousFlagStatus;
}
return query.text;
};

@@ -77,4 +144,11 @@ Postgres.prototype.visit = function(node) {

Postgres.prototype._quoteCharacter = '"';
Postgres.prototype.quote = function(word) {
var q = this._quoteCharacter;
Postgres.prototype.quote = function(word, quoteCharacter) {
var q;
if (quoteCharacter) {
// use the specified quote character if given
q = quoteCharacter;
} else {
q = this._quoteCharacter;
}
return q + word.replace(new RegExp(q,'g'),q+q) + q;

@@ -323,5 +397,18 @@ };

Postgres.prototype.visitSubquery = function(queryNode) {
// create another query builder of the current class to build the subquery
var subQuery = new this._myClass();
// let the subquery modify this instance's params array
subQuery.params = this.params;
subQuery.visitQuery(queryNode);
// pass on the disable parameter placeholder flag
var previousFlagStatus = subQuery._disableParameterPlaceholders;
subQuery._disableParameterPlaceholders = this._disableParameterPlaceholders;
try {
subQuery.visitQuery(queryNode);
} finally {
// restore the flag
subQuery._disableParameterPlaceholders = previousFlagStatus;
}
var alias = queryNode.alias;

@@ -405,10 +492,10 @@ return '(' + subQuery.output.join(' ') + ')' + (alias ? ' ' + alias : '');

Postgres.prototype.visitParameter = function(parameter) {
this.params.push(parameter.value());
return "$"+this.params.length;
// save the value into the parameters array
var value = parameter.value();
this.params.push(value);
return [this._getParameterText(this.params.length, value)];
};
Postgres.prototype.visitDefault = function(parameter) {
var params = this.params;
this.params.push('DEFAULT');
return "$"+params.length;
return ['DEFAULT'];
};

@@ -415,0 +502,0 @@

29

lib/index.js
'use strict';
var _ = require('lodash');
var sliced = require('sliced');
var FunctionCall = require(__dirname + '/node/functionCall');
var functions = require(__dirname + '/functions');
var Query = require(__dirname + '/node/query');
var Table = require(__dirname + '/table');
var _ = require('lodash');
var FunctionCall = require('./node/functionCall');
var functions = require('./functions');
var getDialect = require('./dialect');
var Query = require('./node/query');
var sliced = require('sliced');
var Table = require('./table');

@@ -50,15 +51,3 @@ // default dialect is postgres

Sql.prototype.setDialect = function(dialect) {
switch(dialect.toLowerCase()) {
case 'postgres':
this.dialect = require(__dirname + '/dialect/postgres');
break;
case 'mysql':
this.dialect = require(__dirname + '/dialect/mysql');
break;
case 'sqlite':
this.dialect = require(__dirname + '/dialect/sqlite');
break;
default:
throw new Error(dialect + ' is unsupported');
}
this.dialect = getDialect(dialect);
return this;

@@ -72,5 +61,5 @@ };

module.exports = new Sql('postgres');
module.exports = new Sql(DEFAULT_DIALECT);
module.exports.create = create;
module.exports.Sql = Sql;
module.exports.Table = Table;

6

lib/node/functionCall.js
'use strict';
var _ = require('lodash');
var Node = require(__dirname);
var ParameterNode = require(__dirname + '/parameter');
var _ = require('lodash');
var Node = require(__dirname);
var ParameterNode = require(__dirname + '/parameter');
var valueExpressionMixin = require(__dirname + '/valueExpression');

@@ -7,0 +7,0 @@

35

lib/node/index.js
'use strict';
var util = require('util');
var assert = require('assert');
var _ = require('lodash');
var assert = require('assert');
var getDialect = require('../dialect');
var util = require('util');

@@ -21,13 +23,32 @@ /* jshint unused: false */

Node.prototype.toQuery = function() {
var sql = this.sql || (this.table && this.table.sql);
// Before the change that introduced parallel dialects, every node could be turned
// into a query. The parallel dialects change made it impossible to change some nodes
// into a query because not all nodes are constructed with the sql instance.
var determineDialect = function(query, dialect) {
var sql = query.sql || (query.table && query.table.sql);
var Dialect;
if (sql && sql.dialect)
if (dialect) {
// dialect is specified
Dialect = getDialect(dialect);
} else if (sql && sql.dialect) {
// dialect is not specified, use the dialect from the sql instance
Dialect = sql.dialect;
else {
throw new Error("sql dialect not set properly");
} else {
// dialect is not specified, use the default dialect
Dialect = require('../').dialect;
}
return Dialect;
};
Node.prototype.toQuery = function(dialect) {
var Dialect = determineDialect(this, dialect);
return new Dialect().getQuery(this);
};
Node.prototype.toString = function(dialect) {
var Dialect = determineDialect(this, dialect);
return new Dialect().getString(this);
};
Node.prototype.addAll = function(nodes) {

@@ -34,0 +55,0 @@ for(var i = 0, len = nodes.length; i < len; i++) {

72

lib/node/valueExpression.js

@@ -19,12 +19,12 @@ 'use strict';

var ValueExpressionMixin = module.exports = function() {
var BinaryNode = require(__dirname + '/binary');
var UnaryNode = require(__dirname + '/unary');
var BinaryNode = require(__dirname + '/binary');
var TernaryNode = require(__dirname + '/ternary');
var UnaryNode = require(__dirname + '/unary');
var binaryMethod = function(operator) {
var unaryMethod = function(operator) {
/*jshint unused: false */
return function(val) {
return new BinaryNode({
return new UnaryNode({
left: this.toNode(),
operator: operator,
right: processParams(val)
operator: operator
});

@@ -34,8 +34,8 @@ };

var unaryMethod = function(operator) {
/*jshint unused: false */
var binaryMethod = function(operator) {
return function(val) {
return new UnaryNode({
return new BinaryNode({
left: this.toNode(),
operator: operator
operator: operator,
right: processParams(val)
});

@@ -49,6 +49,6 @@ };

left: this.toNode(),
operator: operator,
middle: processParams(middle),
operator: operator,
right: processParams(right),
separator: separator
separator: separator,
right: processParams(right)
});

@@ -59,25 +59,27 @@ };

return {
isNull : unaryMethod('IS NULL'),
isNotNull: unaryMethod('IS NOT NULL'),
or : binaryMethod('OR'),
and : binaryMethod('AND'),
equals : binaryMethod('='),
equal : binaryMethod('='),
notEquals: binaryMethod('<>'),
notEqual : binaryMethod('<>'),
gt : binaryMethod('>'),
gte : binaryMethod('>='),
lt : binaryMethod('<'),
lte : binaryMethod('<='),
plus : binaryMethod('+'),
minus : binaryMethod('-'),
multiply : binaryMethod('*'),
divide : binaryMethod('/'),
modulo : binaryMethod('%'),
like : binaryMethod('LIKE'),
notLike : binaryMethod('NOT LIKE'),
in : binaryMethod('IN'),
notIn : binaryMethod('NOT IN'),
between : ternaryMethod('BETWEEN', 'AND')
isNull : unaryMethod('IS NULL'),
isNotNull : unaryMethod('IS NOT NULL'),
or : binaryMethod('OR'),
and : binaryMethod('AND'),
equals : binaryMethod('='),
equal : binaryMethod('='),
notEquals : binaryMethod('<>'),
notEqual : binaryMethod('<>'),
gt : binaryMethod('>'),
gte : binaryMethod('>='),
lt : binaryMethod('<'),
lte : binaryMethod('<='),
plus : binaryMethod('+'),
minus : binaryMethod('-'),
multiply : binaryMethod('*'),
divide : binaryMethod('/'),
modulo : binaryMethod('%'),
leftShift : binaryMethod('<<'),
rightShift : binaryMethod('>>'),
like : binaryMethod('LIKE'),
notLike : binaryMethod('NOT LIKE'),
in : binaryMethod('IN'),
notIn : binaryMethod('NOT IN'),
between : ternaryMethod('BETWEEN', 'AND')
};
};

2

package.json

@@ -5,3 +5,3 @@ {

"description": "sql builder",
"version": "0.23.0",
"version": "0.24.0",
"homepage": "https://github.com/brianc/node-sql",

@@ -8,0 +8,0 @@ "repository": {

2

test/binary-clause-tests.js

@@ -28,4 +28,6 @@ /* global test */

assert.equal(Foo.baz.multiply(1).operator, '*');
assert.equal(Foo.baz.leftShift(1).operator, '<<');
assert.equal(Foo.baz.rightShift(1).operator, '>>');
assert.equal(Foo.baz.divide(1).operator, '/');
assert.equal(Foo.baz.modulo(1).operator, '%');
});

321

test/dialects/aggregate-tests.js

@@ -0,3 +1,5 @@

/* global test */
'use strict';
var assert = require('assert');
var Harness = require('./support');

@@ -7,6 +9,15 @@ var post = Harness.definePostTable();

Harness.test({
query : post.select(post.count()),
pg : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
sqlite: 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
mysql : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
query: post.select(post.count()),
pg: {
text : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_count" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_count" FROM "post"'
},
mysql: {
text : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
string: 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`'
},
params: []

@@ -16,6 +27,15 @@ });

Harness.test({
query : post.select(post.count('post_count')),
pg : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
sqlite: 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
msyql : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
query: post.select(post.count('post_count')),
pg: {
text : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_count" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post".*) AS "post_count" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_count" FROM "post"'
},
msyql: {
text : 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`',
string: 'SELECT COUNT(`post`.*) AS `post_count` FROM `post`'
},
params: []

@@ -25,6 +45,15 @@ });

Harness.test({
query : post.select(post.count().as('post_amount')),
pg : 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
sqlite: 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
mysql : 'SELECT COUNT(`post`.*) AS `post_amount` FROM `post`',
query: post.select(post.count().as('post_amount')),
pg: {
text : 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_amount" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post".*) AS "post_amount" FROM "post"',
string: 'SELECT COUNT("post".*) AS "post_amount" FROM "post"'
},
mysql: {
text : 'SELECT COUNT(`post`.*) AS `post_amount` FROM `post`',
string: 'SELECT COUNT(`post`.*) AS `post_amount` FROM `post`'
},
params: []

@@ -34,6 +63,15 @@ });

Harness.test({
query : post.select(post.content.count()),
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
query: post.select(post.content.count()),
pg: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
mysql: {
text : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
string: 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`'
},
params: []

@@ -43,6 +81,15 @@ });

Harness.test({
query : post.select(post.content.count('content_count')),
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
query: post.select(post.content.count('content_count')),
pg: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
mysql: {
text : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
string: 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`'
},
params: []

@@ -52,6 +99,15 @@ });

Harness.test({
query : post.select(post.content.count().as('content_count')),
pg : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
sqlite: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
mysql : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
query: post.select(post.content.count().as('content_count')),
pg: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
sqlite: {
text : 'SELECT COUNT("post"."content") AS "content_count" FROM "post"',
string: 'SELECT COUNT("post"."content") AS "content_count" FROM "post"'
},
mysql: {
text : 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`',
string: 'SELECT COUNT(`post`.`content`) AS `content_count` FROM `post`'
},
params: []

@@ -61,6 +117,15 @@ });

Harness.test({
query : post.select(post.id.min()),
pg : 'SELECT MIN("post"."id") AS "id_min" FROM "post"',
sqlite: 'SELECT MIN("post"."id") AS "id_min" FROM "post"',
mysql : 'SELECT MIN(`post`.`id`) AS `id_min` FROM `post`',
query: post.select(post.id.min()),
pg: {
text : 'SELECT MIN("post"."id") AS "id_min" FROM "post"',
string: 'SELECT MIN("post"."id") AS "id_min" FROM "post"'
},
sqlite: {
text : 'SELECT MIN("post"."id") AS "id_min" FROM "post"',
string: 'SELECT MIN("post"."id") AS "id_min" FROM "post"'
},
mysql: {
text : 'SELECT MIN(`post`.`id`) AS `id_min` FROM `post`',
string: 'SELECT MIN(`post`.`id`) AS `id_min` FROM `post`'
},
params: []

@@ -70,6 +135,15 @@ });

Harness.test({
query : post.select(post.id.min().as('min_id')),
pg : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
sqlite: 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
mysql : 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`',
query: post.select(post.id.min().as('min_id')),
pg: {
text : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
string: 'SELECT MIN("post"."id") AS "min_id" FROM "post"'
},
sqlite: {
text : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
string: 'SELECT MIN("post"."id") AS "min_id" FROM "post"'
},
mysql: {
text : 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`',
string: 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`'
},
params: []

@@ -79,6 +153,15 @@ });

Harness.test({
query : post.select(post.id.min('min_id')),
pg : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
sqlite: 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
mysql : 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`',
query: post.select(post.id.min('min_id')),
pg: {
text : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
string: 'SELECT MIN("post"."id") AS "min_id" FROM "post"'
},
sqlite: {
text : 'SELECT MIN("post"."id") AS "min_id" FROM "post"',
string: 'SELECT MIN("post"."id") AS "min_id" FROM "post"'
},
mysql: {
text : 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`',
string: 'SELECT MIN(`post`.`id`) AS `min_id` FROM `post`'
},
params: []

@@ -88,6 +171,15 @@ });

Harness.test({
query : post.select(post.id.max()),
pg : 'SELECT MAX("post"."id") AS "id_max" FROM "post"',
sqlite: 'SELECT MAX("post"."id") AS "id_max" FROM "post"',
mysql : 'SELECT MAX(`post`.`id`) AS `id_max` FROM `post`',
query: post.select(post.id.max()),
pg: {
text : 'SELECT MAX("post"."id") AS "id_max" FROM "post"',
string: 'SELECT MAX("post"."id") AS "id_max" FROM "post"'
},
sqlite: {
text : 'SELECT MAX("post"."id") AS "id_max" FROM "post"',
string: 'SELECT MAX("post"."id") AS "id_max" FROM "post"'
},
mysql: {
text : 'SELECT MAX(`post`.`id`) AS `id_max` FROM `post`',
string: 'SELECT MAX(`post`.`id`) AS `id_max` FROM `post`'
},
params: []

@@ -97,6 +189,15 @@ });

Harness.test({
query : post.select(post.id.max().as('max_id')),
pg : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
sqlite: 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
mysql : 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`',
query: post.select(post.id.max().as('max_id')),
pg: {
text : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
string: 'SELECT MAX("post"."id") AS "max_id" FROM "post"'
},
sqlite: {
text : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
string: 'SELECT MAX("post"."id") AS "max_id" FROM "post"'
},
mysql: {
text : 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`',
string: 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`'
},
params: []

@@ -106,50 +207,112 @@ });

Harness.test({
query : post.select(post.id.max('max_id')),
pg : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
sqlite: 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
mysql : 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`',
query: post.select(post.id.max('max_id')),
pg: {
text : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
string: 'SELECT MAX("post"."id") AS "max_id" FROM "post"'
},
sqlite: {
text : 'SELECT MAX("post"."id") AS "max_id" FROM "post"',
string: 'SELECT MAX("post"."id") AS "max_id" FROM "post"'
},
mysql: {
text : 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`',
string: 'SELECT MAX(`post`.`id`) AS `max_id` FROM `post`'
},
params: []
});
Harness.test({
query : post.select(post.id.sum()),
pg : 'SELECT SUM("post"."id") AS "id_sum" FROM "post"',
sqllite : 'SELECT SUM("post"."id") AS "id_sum" FROM "post"',
mysql : 'SELECT SUM(`post`.`id`) AS `id_sum` FROM `post`',
Harness.test({
query: post.select(post.id.sum()),
pg: {
text : 'SELECT SUM("post"."id") AS "id_sum" FROM "post"',
string: 'SELECT SUM("post"."id") AS "id_sum" FROM "post"'
},
sqlite: {
text : 'SELECT SUM("post"."id") AS "id_sum" FROM "post"',
string: 'SELECT SUM("post"."id") AS "id_sum" FROM "post"'
},
mysql: {
text : 'SELECT SUM(`post`.`id`) AS `id_sum` FROM `post`',
string: 'SELECT SUM(`post`.`id`) AS `id_sum` FROM `post`'
},
});
Harness.test({
query : post.select(post.id.sum().as('sum_id')),
pg : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
sqllite : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
mysql : 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`',
Harness.test({
query: post.select(post.id.sum().as('sum_id')),
pg: {
text : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
string: 'SELECT SUM("post"."id") AS "sum_id" FROM "post"'
},
sqlite: {
text : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
string: 'SELECT SUM("post"."id") AS "sum_id" FROM "post"'
},
mysql: {
text : 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`',
string: 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`'
},
});
Harness.test({
query : post.select(post.id.sum('sum_id')),
pg : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
sqllite : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
mysql : 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`',
Harness.test({
query: post.select(post.id.sum('sum_id')),
pg: {
text : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
string: 'SELECT SUM("post"."id") AS "sum_id" FROM "post"'
},
sqlite: {
text : 'SELECT SUM("post"."id") AS "sum_id" FROM "post"',
string: 'SELECT SUM("post"."id") AS "sum_id" FROM "post"'
},
mysql: {
text : 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`',
string: 'SELECT SUM(`post`.`id`) AS `sum_id` FROM `post`'
},
});
Harness.test({
query : post.select(post.id.avg()),
pg : 'SELECT AVG("post"."id") AS "id_avg" FROM "post"',
sqllite : 'SELECT AVG("post"."id") AS "id_avg" FROM "post"',
mysql : 'SELECT AVG(`post`.`id`) AS `id_avg` FROM `post`',
Harness.test({
query: post.select(post.id.avg()),
pg: {
text : 'SELECT AVG("post"."id") AS "id_avg" FROM "post"',
string: 'SELECT AVG("post"."id") AS "id_avg" FROM "post"'
},
sqlite: {
text : 'SELECT AVG("post"."id") AS "id_avg" FROM "post"',
string: 'SELECT AVG("post"."id") AS "id_avg" FROM "post"'
},
mysql: {
text : 'SELECT AVG(`post`.`id`) AS `id_avg` FROM `post`',
string: 'SELECT AVG(`post`.`id`) AS `id_avg` FROM `post`'
},
});
Harness.test({
query : post.select(post.id.avg().as('avg_id')),
pg : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
sqllite : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
mysql : 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`',
Harness.test({
query: post.select(post.id.avg().as('avg_id')),
pg: {
text : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
string: 'SELECT AVG("post"."id") AS "avg_id" FROM "post"'
},
sqlite: {
text : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
string: 'SELECT AVG("post"."id") AS "avg_id" FROM "post"'
},
mysql: {
text : 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`',
string: 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`'
},
});
Harness.test({
query : post.select(post.id.avg('avg_id')),
pg : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
sqllite : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
mysql : 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`',
Harness.test({
query: post.select(post.id.avg('avg_id')),
pg: {
text : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
string: 'SELECT AVG("post"."id") AS "avg_id" FROM "post"'
},
sqlite: {
text : 'SELECT AVG("post"."id") AS "avg_id" FROM "post"',
string: 'SELECT AVG("post"."id") AS "avg_id" FROM "post"'
},
mysql: {
text : 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`',
string: 'SELECT AVG(`post`.`id`) AS `avg_id` FROM `post`'
},
});

57

test/dialects/alias-tests.js

@@ -9,23 +9,50 @@ 'use strict';

Harness.test({
query : customer.select(customer.name.isNull().as('nameIsNull')),
pg : 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"',
sqlite : 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"',
mysql : 'SELECT (`customer`.`name` IS NULL) AS `nameIsNull` FROM `customer`',
params : []
query: customer.select(customer.name.isNull().as('nameIsNull')),
pg: {
text : 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"',
string: 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"'
},
sqlite: {
text : 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"',
string: 'SELECT ("customer"."name" IS NULL) AS "nameIsNull" FROM "customer"'
},
mysql: {
text : 'SELECT (`customer`.`name` IS NULL) AS `nameIsNull` FROM `customer`',
string: 'SELECT (`customer`.`name` IS NULL) AS `nameIsNull` FROM `customer`'
},
params: []
});
Harness.test({
query : customer.select(customer.name.plus(customer.age).as('nameAndAge')).where(customer.age.gt(10).and(customer.age.lt(20))),
pg : 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > $1) AND ("customer"."age" < $2))',
sqlite : 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > $1) AND ("customer"."age" < $2))',
mysql : 'SELECT (`customer`.`name` + `customer`.`age`) AS `nameAndAge` FROM `customer` WHERE ((`customer`.`age` > ?) AND (`customer`.`age` < ?))',
params : [10, 20]
query: customer.select(customer.name.plus(customer.age).as('nameAndAge')).where(customer.age.gt(10).and(customer.age.lt(20))),
pg: {
text : 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > $1) AND ("customer"."age" < $2))',
string: 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > 10) AND ("customer"."age" < 20))'
},
sqlite: {
text : 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > $1) AND ("customer"."age" < $2))',
string: 'SELECT ("customer"."name" + "customer"."age") AS "nameAndAge" FROM "customer" WHERE (("customer"."age" > 10) AND ("customer"."age" < 20))'
},
mysql: {
text : 'SELECT (`customer`.`name` + `customer`.`age`) AS `nameAndAge` FROM `customer` WHERE ((`customer`.`age` > ?) AND (`customer`.`age` < ?))',
string: 'SELECT (`customer`.`name` + `customer`.`age`) AS `nameAndAge` FROM `customer` WHERE ((`customer`.`age` > 10) AND (`customer`.`age` < 20))'
},
params: [10, 20]
});
Harness.test({
query : customer.select(customer.age.between(10, 20).as('ageBetween')),
pg : 'SELECT ("customer"."age" BETWEEN $1 AND $2) AS "ageBetween" FROM "customer"',
sqlite : 'SELECT ("customer"."age" BETWEEN $1 AND $2) AS "ageBetween" FROM "customer"',
mysql : 'SELECT (`customer`.`age` BETWEEN ? AND ?) AS `ageBetween` FROM `customer`',
params : [10, 20]
query: customer.select(customer.age.between(10, 20).as('ageBetween')),
pg: {
text : 'SELECT ("customer"."age" BETWEEN $1 AND $2) AS "ageBetween" FROM "customer"',
string: 'SELECT ("customer"."age" BETWEEN 10 AND 20) AS "ageBetween" FROM "customer"'
},
sqlite: {
text : 'SELECT ("customer"."age" BETWEEN $1 AND $2) AS "ageBetween" FROM "customer"',
string: 'SELECT ("customer"."age" BETWEEN 10 AND 20) AS "ageBetween" FROM "customer"'
},
mysql: {
text : 'SELECT (`customer`.`age` BETWEEN ? AND ?) AS `ageBetween` FROM `customer`',
string: 'SELECT (`customer`.`age` BETWEEN 10 AND 20) AS `ageBetween` FROM `customer`'
},
params: [10, 20]
});

163

test/dialects/alter-table-tests.js

@@ -8,4 +8,7 @@ 'use strict';

Harness.test({
query : post.alter().dropColumn(post.content),
pg : 'ALTER TABLE "post" DROP COLUMN "content"',
query: post.alter().dropColumn(post.content),
pg: {
text : 'ALTER TABLE "post" DROP COLUMN "content"',
string: 'ALTER TABLE "post" DROP COLUMN "content"'
},
sqlite: {

@@ -15,3 +18,6 @@ text : 'Sqlite cannot drop columns',

},
mysql : 'ALTER TABLE `post` DROP COLUMN `content`',
mysql: {
text : 'ALTER TABLE `post` DROP COLUMN `content`',
string: 'ALTER TABLE `post` DROP COLUMN `content`'
},
params: []

@@ -21,4 +27,7 @@ });

Harness.test({
query : post.alter().dropColumn(post.content).dropColumn(post.userId),
pg : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
query: post.alter().dropColumn(post.content).dropColumn(post.userId),
pg: {
text : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
string: 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"'
},
sqlite: {

@@ -28,3 +37,6 @@ text : 'Sqlite cannot drop columns',

},
mysql : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
mysql: {
text : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
string: 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`'
},
params: []

@@ -34,4 +46,7 @@ });

Harness.test({
query : post.alter().dropColumn('content').dropColumn('userId'),
pg : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
query: post.alter().dropColumn('content').dropColumn('userId'),
pg: {
text : 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"',
string: 'ALTER TABLE "post" DROP COLUMN "content", DROP COLUMN "userId"'
},
sqlite: {

@@ -41,3 +56,6 @@ text : 'Sqlite cannot drop columns',

},
mysql : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
mysql: {
text : 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`',
string: 'ALTER TABLE `post` DROP COLUMN `content`, DROP COLUMN `userId`'
},
params: []

@@ -47,6 +65,15 @@ });

Harness.test({
query : post.alter().rename('posts'),
pg : 'ALTER TABLE "post" RENAME TO "posts"',
sqlite: 'ALTER TABLE "post" RENAME TO "posts"',
mysql : 'ALTER TABLE `post` RENAME TO `posts`',
query: post.alter().rename('posts'),
pg: {
text : 'ALTER TABLE "post" RENAME TO "posts"',
string: 'ALTER TABLE "post" RENAME TO "posts"'
},
sqlite: {
text : 'ALTER TABLE "post" RENAME TO "posts"',
string: 'ALTER TABLE "post" RENAME TO "posts"'
},
mysql: {
text : 'ALTER TABLE `post` RENAME TO `posts`',
string: 'ALTER TABLE `post` RENAME TO `posts`'
},
params: []

@@ -58,15 +85,25 @@ });

columns: [{
name: 'id',
dataType: 'varchar(100)'
}, {
name: 'userId',
dataType: 'varchar(100)'
}]
name: 'id',
dataType: 'varchar(100)'
}, {
name: 'userId',
dataType: 'varchar(100)'
}
]
});
Harness.test({
query : group.alter().addColumn(group.id),
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
sqlite: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100)',
query: group.alter().addColumn(group.id),
pg: {
text : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
string: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)'
},
sqlite: {
text : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)',
string: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100)'
},
mysql: {
text : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100)',
string: 'ALTER TABLE `group` ADD COLUMN `id` varchar(100)'
},
params: []

@@ -76,4 +113,7 @@ });

Harness.test({
query : group.alter().addColumn(group.id).addColumn(group.userId),
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
query: group.alter().addColumn(group.id).addColumn(group.userId),
pg: {
text : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
string: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)'
},
sqlite: {

@@ -83,3 +123,6 @@ text : 'Sqlite cannot add more than one column at a time',

},
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
mysql: {
text : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
string: 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)'
},
params: []

@@ -89,4 +132,7 @@ });

Harness.test({
query : group.alter().addColumn('id', 'varchar(100)').addColumn('userId', 'varchar(100)'),
pg : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
query: group.alter().addColumn('id', 'varchar(100)').addColumn('userId', 'varchar(100)'),
pg: {
text : 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)',
string: 'ALTER TABLE "group" ADD COLUMN "id" varchar(100), ADD COLUMN "userId" varchar(100)'
},
sqlite: {

@@ -96,3 +142,6 @@ text : 'Sqlite cannot add more than one column at a time',

},
mysql : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
mysql: {
text : 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)',
string: 'ALTER TABLE `group` ADD COLUMN `id` varchar(100), ADD COLUMN `userId` varchar(100)'
},
params: []

@@ -102,6 +151,9 @@ });

Harness.test({
query : group.alter().renameColumn('userId', 'newUserId'),
pg : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"',
mysql : {
text: 'Mysql requires data type for renaming a column',
query: group.alter().renameColumn('userId', 'newUserId'),
pg: {
text : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"',
string: 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"'
},
mysql: {
text : 'Mysql requires data type for renaming a column',
throws: true

@@ -117,4 +169,7 @@ },

Harness.test({
query : group.alter().renameColumn(group.userId, 'newUserId'),
pg : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"',
query: group.alter().renameColumn(group.userId, 'newUserId'),
pg: {
text : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"',
string: 'ALTER TABLE "group" RENAME COLUMN "userId" TO "newUserId"'
},
sqlite: {

@@ -124,3 +179,6 @@ text : 'Sqlite cannot rename columns',

},
mysql : 'ALTER TABLE `group` CHANGE COLUMN `userId` `newUserId` varchar(100)',
mysql: {
text : 'ALTER TABLE `group` CHANGE COLUMN `userId` `newUserId` varchar(100)',
string: 'ALTER TABLE `group` CHANGE COLUMN `userId` `newUserId` varchar(100)'
},
params: []

@@ -130,4 +188,7 @@ });

Harness.test({
query : group.alter().renameColumn('userId', group.id),
pg : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "id"',
query: group.alter().renameColumn('userId', group.id),
pg: {
text : 'ALTER TABLE "group" RENAME COLUMN "userId" TO "id"',
string: 'ALTER TABLE "group" RENAME COLUMN "userId" TO "id"'
},
sqlite: {

@@ -137,3 +198,6 @@ text : 'Sqlite cannot rename columns',

},
mysql : 'ALTER TABLE `group` CHANGE COLUMN `userId` `id` varchar(100)',
mysql: {
text : 'ALTER TABLE `group` CHANGE COLUMN `userId` `id` varchar(100)',
string: 'ALTER TABLE `group` CHANGE COLUMN `userId` `id` varchar(100)'
},
params: []

@@ -143,13 +207,20 @@ });

var UserWithSignature = Table.define({
name: 'UserWithSignature',
name: 'UserWithSignature',
columns: [{
name: 'Signature',
dataType: "VARCHAR(255) NOT NULL DEFAULT 'Signature'"
}]
name: 'Signature',
dataType: "VARCHAR(255) NOT NULL DEFAULT 'Signature'"
}
]
});
Harness.test({
query: UserWithSignature.alter().renameColumn(UserWithSignature.get('Signature'), 'sig'),
pg: 'ALTER TABLE "UserWithSignature" RENAME COLUMN "Signature" TO "sig"',
mysql: 'ALTER TABLE `UserWithSignature` CHANGE COLUMN `Signature` `sig` VARCHAR(255) NOT NULL DEFAULT \'Signature\'',
query: UserWithSignature.alter().renameColumn(UserWithSignature.get('Signature'), 'sig'),
pg: {
text : 'ALTER TABLE "UserWithSignature" RENAME COLUMN "Signature" TO "sig"',
string: 'ALTER TABLE "UserWithSignature" RENAME COLUMN "Signature" TO "sig"'
},
mysql: {
text : 'ALTER TABLE `UserWithSignature` CHANGE COLUMN `Signature` `sig` VARCHAR(255) NOT NULL DEFAULT \'Signature\'',
string: 'ALTER TABLE `UserWithSignature` CHANGE COLUMN `Signature` `sig` VARCHAR(255) NOT NULL DEFAULT \'Signature\''
},
sqlite: {

@@ -156,0 +227,0 @@ text : 'Sqlite cannot rename columns',

53

test/dialects/binary-clause-tests.js

@@ -9,6 +9,15 @@ 'use strict';

Harness.test({
query : customer.select(customer.name.plus(customer.age)),
pg : 'SELECT ("customer"."name" + "customer"."age") FROM "customer"',
sqlite: 'SELECT ("customer"."name" + "customer"."age") FROM "customer"',
mysql : 'SELECT (`customer`.`name` + `customer`.`age`) FROM `customer`',
query: customer.select(customer.name.plus(customer.age)),
pg: {
text : 'SELECT ("customer"."name" + "customer"."age") FROM "customer"',
string: 'SELECT ("customer"."name" + "customer"."age") FROM "customer"'
},
sqlite: {
text : 'SELECT ("customer"."name" + "customer"."age") FROM "customer"',
string: 'SELECT ("customer"."name" + "customer"."age") FROM "customer"'
},
mysql: {
text : 'SELECT (`customer`.`name` + `customer`.`age`) FROM `customer`',
string: 'SELECT (`customer`.`name` + `customer`.`age`) FROM `customer`'
},
params: []

@@ -18,6 +27,15 @@ });

Harness.test({
query : post.select(post.content.plus('!')).where(post.userId.in(customer.subQuery().select(customer.id))),
pg : 'SELECT ("post"."content" + $1) FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))',
sqlite: 'SELECT ("post"."content" + $1) FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))',
mysql : 'SELECT (`post`.`content` + ?) FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer`))',
query: post.select(post.content.plus('!')).where(post.userId. in (customer.subQuery().select(customer.id))),
pg: {
text : 'SELECT ("post"."content" + $1) FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))',
string: 'SELECT ("post"."content" + \'!\') FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))'
},
sqlite: {
text : 'SELECT ("post"."content" + $1) FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))',
string: 'SELECT ("post"."content" + \'!\') FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer"))'
},
mysql: {
text : 'SELECT (`post`.`content` + ?) FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer`))',
string: 'SELECT (`post`.`content` + \'!\') FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer`))'
},
params: ['!']

@@ -27,7 +45,16 @@ });

Harness.test({
query : post.select(post.id.plus(': ').plus(post.content)).where(post.userId.notIn(customer.subQuery().select(customer.id))),
pg : 'SELECT (("post"."id" + $1) + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))',
sqlite : 'SELECT (("post"."id" + $1) + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))',
mysql : 'SELECT ((`post`.`id` + ?) + `post`.`content`) FROM `post` WHERE (`post`.`userId` NOT IN (SELECT `customer`.`id` FROM `customer`))',
params : [': ']
query: post.select(post.id.plus(': ').plus(post.content)).where(post.userId.notIn(customer.subQuery().select(customer.id))),
pg: {
text : 'SELECT (("post"."id" + $1) + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))',
string: 'SELECT (("post"."id" + \': \') + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))'
},
sqlite: {
text : 'SELECT (("post"."id" + $1) + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))',
string: 'SELECT (("post"."id" + \': \') + "post"."content") FROM "post" WHERE ("post"."userId" NOT IN (SELECT "customer"."id" FROM "customer"))'
},
mysql: {
text : 'SELECT ((`post`.`id` + ?) + `post`.`content`) FROM `post` WHERE (`post`.`userId` NOT IN (SELECT `customer`.`id` FROM `customer`))',
string: 'SELECT ((`post`.`id` + \': \') + `post`.`content`) FROM `post` WHERE (`post`.`userId` NOT IN (SELECT `customer`.`id` FROM `customer`))'
},
params: [': ']
});

80

test/dialects/clause-ordering-tests.js

@@ -9,6 +9,15 @@ 'use strict';

Harness.test({
query : user.from(user.join(post).on(user.id.equals(post.userId))).select(user.name, post.content),
pg : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
query: user.from(user.join(post).on(user.id.equals(post.userId))).select(user.name, post.content),
pg: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
},
sqlite: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)',
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
},
});

@@ -18,6 +27,17 @@

Harness.test({
query : user.where({name: ''}).from(user).select(user.id),
pg : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
query: user.where({
name: ''
}).from(user).select(user.id),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'\')'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'\')'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = \'\')'
},
params: ['']

@@ -28,9 +48,20 @@ });

Harness.test({
query : user
.select(user.name, post.content)
.from(user.join(post).on(user.id.equals(post.userId)))
.where({ name: '' }),
pg : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) WHERE (`user`.`name` = ?)',
query: user
.select(user.name, post.content)
.from(user.join(post).on(user.id.equals(post.userId)))
.where({
name: ''
}),
pg: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = \'\')'
},
sqlite: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") WHERE ("user"."name" = \'\')'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) WHERE (`user`.`name` = \'\')'
},
params: ['']

@@ -41,7 +72,18 @@ });

Harness.test({
query : user.select(user.id).from(user).where({name: ''}),
pg : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
query: user.select(user.id).from(user).where({
name: ''
}),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'\')'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'\')'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = \'\')'
},
params: ['']
});

123

test/dialects/create-table-tests.js

@@ -9,15 +9,25 @@ 'use strict';

columns: [{
name: 'id',
dataType: 'varchar(100)'
}, {
name: 'user_id',
dataType: 'varchar(100)'
}]
name: 'id',
dataType: 'varchar(100)'
}, {
name: 'user_id',
dataType: 'varchar(100)'
}
]
});
Harness.test({
query : group.create(),
pg : 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
sqlite: 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
mysql : 'CREATE TABLE `group` (`id` varchar(100), `user_id` varchar(100))',
query: group.create(),
pg: {
text : 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
string: 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))'
},
sqlite: {
text : 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))',
string: 'CREATE TABLE "group" ("id" varchar(100), "user_id" varchar(100))'
},
mysql: {
text : 'CREATE TABLE `group` (`id` varchar(100), `user_id` varchar(100))',
string: 'CREATE TABLE `group` (`id` varchar(100), `user_id` varchar(100))'
},
params: []

@@ -27,6 +37,15 @@ });

Harness.test({
query : group.create().ifNotExists(),
pg : 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
sqlite: 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
mysql : 'CREATE TABLE IF NOT EXISTS `group` (`id` varchar(100), `user_id` varchar(100))',
query: group.create().ifNotExists(),
pg: {
text : 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
string: 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))'
},
sqlite: {
text : 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))',
string: 'CREATE TABLE IF NOT EXISTS "group" ("id" varchar(100), "user_id" varchar(100))'
},
mysql: {
text : 'CREATE TABLE IF NOT EXISTS `group` (`id` varchar(100), `user_id` varchar(100))',
string: 'CREATE TABLE IF NOT EXISTS `group` (`id` varchar(100), `user_id` varchar(100))'
},
params: []

@@ -36,20 +55,72 @@ });

Harness.test({
query : Table.define({ name: 'user', columns: [{ name: 'id', dataType: 'varchar(100)' }], engine: 'InnoDB' }).create(),
pg : 'CREATE TABLE "user" ("id" varchar(100))',
sqlite: 'CREATE TABLE "user" ("id" varchar(100))',
mysql : 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=InnoDB'
query: Table.define({
name: 'user',
columns: [{
name: 'id',
dataType: 'varchar(100)'
}
],
engine: 'InnoDB'
}).create(),
pg: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
sqlite: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
mysql: {
text : 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=InnoDB',
string: 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=InnoDB'
}
});
Harness.test({
query : Table.define({ name: 'user', columns: [{ name: 'id', dataType: 'varchar(100)' }], charset: 'latin1' }).create(),
pg : 'CREATE TABLE "user" ("id" varchar(100))',
sqlite: 'CREATE TABLE "user" ("id" varchar(100))',
mysql : 'CREATE TABLE `user` (`id` varchar(100)) DEFAULT CHARSET=latin1'
query: Table.define({
name: 'user',
columns: [{
name: 'id',
dataType: 'varchar(100)'
}
],
charset: 'latin1'
}).create(),
pg: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
sqlite: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
mysql: {
text : 'CREATE TABLE `user` (`id` varchar(100)) DEFAULT CHARSET=latin1',
string: 'CREATE TABLE `user` (`id` varchar(100)) DEFAULT CHARSET=latin1'
}
});
Harness.test({
query : Table.define({ name: 'user', columns: [{ name: 'id', dataType: 'varchar(100)' }], engine: 'MyISAM', charset: 'latin1' }).create(),
pg : 'CREATE TABLE "user" ("id" varchar(100))',
sqlite: 'CREATE TABLE "user" ("id" varchar(100))',
mysql : 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=MyISAM DEFAULT CHARSET=latin1'
query: Table.define({
name: 'user',
columns: [{
name: 'id',
dataType: 'varchar(100)'
}
],
engine: 'MyISAM',
charset: 'latin1'
}).create(),
pg: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
sqlite: {
text : 'CREATE TABLE "user" ("id" varchar(100))',
string: 'CREATE TABLE "user" ("id" varchar(100))'
},
mysql: {
text : 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=MyISAM DEFAULT CHARSET=latin1',
string: 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=MyISAM DEFAULT CHARSET=latin1'
}
});

76

test/dialects/delete-tests.js

@@ -7,14 +7,34 @@ 'use strict';

Harness.test({
query : post.delete().where(post.content.equals('')),
pg : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
sqlite: 'DELETE FROM "post" WHERE ("post"."content" = $1)',
mysql : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
params: ['']
query: post.delete().where(post.content.equals("hello's world")),
pg: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'hello\'\'s world\')'
},
sqlite: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'hello\'\'s world\')'
},
mysql: {
text : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
string: 'DELETE FROM `post` WHERE (`post`.`content` = \'hello\'\'s world\')'
},
params: ["hello's world"]
});
Harness.test({
query : post.delete().where({content: ''}),
pg : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
sqlite: 'DELETE FROM "post" WHERE ("post"."content" = $1)',
mysql : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
query: post.delete().where({
content: ''
}),
pg: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'\')'
},
sqlite: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'\')'
},
mysql: {
text : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
string: 'DELETE FROM `post` WHERE (`post`.`content` = \'\')'
},
params: ['']

@@ -24,6 +44,17 @@ });

Harness.test({
query : post.delete({content: ''}),
pg : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
sqlite: 'DELETE FROM "post" WHERE ("post"."content" = $1)',
mysql : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
query: post.delete({
content: ''
}),
pg: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'\')'
},
sqlite: {
text : 'DELETE FROM "post" WHERE ("post"."content" = $1)',
string: 'DELETE FROM "post" WHERE ("post"."content" = \'\')'
},
mysql: {
text : 'DELETE FROM `post` WHERE (`post`.`content` = ?)',
string: 'DELETE FROM `post` WHERE (`post`.`content` = \'\')'
},
params: ['']

@@ -33,7 +64,18 @@ });

Harness.test({
query : post.delete({content: ''}).or(post.content.isNull()),
pg : 'DELETE FROM "post" WHERE (("post"."content" = $1) OR ("post"."content" IS NULL))',
sqlite: 'DELETE FROM "post" WHERE (("post"."content" = $1) OR ("post"."content" IS NULL))',
mysql : 'DELETE FROM `post` WHERE ((`post`.`content` = ?) OR (`post`.`content` IS NULL))',
query: post.delete({
content: ''
}).or(post.content.isNull()),
pg: {
text : 'DELETE FROM "post" WHERE (("post"."content" = $1) OR ("post"."content" IS NULL))',
string: 'DELETE FROM "post" WHERE (("post"."content" = \'\') OR ("post"."content" IS NULL))'
},
sqlite: {
text : 'DELETE FROM "post" WHERE (("post"."content" = $1) OR ("post"."content" IS NULL))',
string: 'DELETE FROM "post" WHERE (("post"."content" = \'\') OR ("post"."content" IS NULL))'
},
mysql: {
text : 'DELETE FROM `post` WHERE ((`post`.`content` = ?) OR (`post`.`content` IS NULL))',
string: 'DELETE FROM `post` WHERE ((`post`.`content` = \'\') OR (`post`.`content` IS NULL))'
},
params: ['']
});

32

test/dialects/distinct-tests.js

@@ -7,9 +7,33 @@ 'use strict';

Harness.test({
query : user.select(user.id.distinct()),
pg : 'SELECT DISTINCT("user"."id") FROM "user"'
query: user.select(user.id.distinct()),
pg: {
text : 'SELECT DISTINCT("user"."id") FROM "user"',
string: 'SELECT DISTINCT("user"."id") FROM "user"'
},
sqlite: {
text : 'SELECT DISTINCT("user"."id") FROM "user"',
string: 'SELECT DISTINCT("user"."id") FROM "user"'
},
mysql: {
text : 'SELECT DISTINCT(`user`.`id`) FROM `user`',
string: 'SELECT DISTINCT(`user`.`id`) FROM `user`'
},
params: []
});
Harness.test({
query : user.select(user.id.count().distinct().as('count')),
pg : 'SELECT COUNT(DISTINCT("user"."id")) AS "count" FROM "user"'
query: user.select(user.id.count().distinct().as('count')),
pg: {
text : 'SELECT COUNT(DISTINCT("user"."id")) AS "count" FROM "user"',
string: 'SELECT COUNT(DISTINCT("user"."id")) AS "count" FROM "user"'
},
sqlite: {
text : 'SELECT COUNT(DISTINCT("user"."id")) AS "count" FROM "user"',
string: 'SELECT COUNT(DISTINCT("user"."id")) AS "count" FROM "user"'
},
mysql: {
text : 'SELECT COUNT(DISTINCT(`user`.`id`)) AS `count` FROM `user`',
string: 'SELECT COUNT(DISTINCT(`user`.`id`)) AS `count` FROM `user`'
},
params: []
});

36

test/dialects/drop-table-tests.js

@@ -7,6 +7,15 @@ 'use strict';

Harness.test({
query : post.drop(),
pg : 'DROP TABLE "post"',
sqlite: 'DROP TABLE "post"',
mysql : 'DROP TABLE `post`',
query: post.drop(),
pg: {
text : 'DROP TABLE "post"',
string: 'DROP TABLE "post"'
},
sqlite: {
text : 'DROP TABLE "post"',
string: 'DROP TABLE "post"'
},
mysql: {
text : 'DROP TABLE `post`',
string: 'DROP TABLE `post`'
},
params: []

@@ -16,7 +25,16 @@ });

Harness.test({
query : post.drop().ifExists(),
pg : 'DROP TABLE IF EXISTS "post"',
sqlite: 'DROP TABLE IF EXISTS "post"',
mysql : 'DROP TABLE IF EXISTS `post`',
query: post.drop().ifExists(),
pg: {
text : 'DROP TABLE IF EXISTS "post"',
string: 'DROP TABLE IF EXISTS "post"'
},
sqlite: {
text : 'DROP TABLE IF EXISTS "post"',
string: 'DROP TABLE IF EXISTS "post"'
},
mysql: {
text : 'DROP TABLE IF EXISTS `post`',
string: 'DROP TABLE IF EXISTS `post`'
},
params: []
});
});

17

test/dialects/from-clause-tests.js

@@ -8,6 +8,15 @@ 'use strict';

Harness.test({
query : user.select(user.star()).from(user).from(post),
pg : 'SELECT "user".* FROM "user" , "post"',
sqlite: 'SELECT "user".* FROM "user" , "post"',
mysql : 'SELECT `user`.* FROM `user` , `post`'
query: user.select(user.star()).from(user).from(post),
pg: {
text : 'SELECT "user".* FROM "user" , "post"',
string: 'SELECT "user".* FROM "user" , "post"'
},
sqlite: {
text : 'SELECT "user".* FROM "user" , "post"',
string: 'SELECT "user".* FROM "user" , "post"'
},
mysql: {
text : 'SELECT `user`.* FROM `user` , `post`',
string: 'SELECT `user`.* FROM `user` , `post`'
}
});

85

test/dialects/group-by-tests.js

@@ -7,6 +7,15 @@ 'use strict';

Harness.test({
query : post.select(post.content).group(post.userId),
pg : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"',
sqlite: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"',
mysql : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`',
query: post.select(post.content).group(post.userId),
pg: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId"'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`',
string: 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`'
},
params: []

@@ -16,6 +25,15 @@ });

Harness.test({
query : post.select(post.content).group(post.userId, post.id),
pg : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
sqlite: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
mysql : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`',
query: post.select(post.content).group(post.userId, post.id),
pg: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`',
string: 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`'
},
params: []

@@ -25,6 +43,15 @@ });

Harness.test({
query : post.select(post.content.arrayAgg()).group(post.userId),
pg : 'SELECT array_agg("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"',
sqlite: 'SELECT GROUP_CONCAT("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"',
mysql : 'SELECT GROUP_CONCAT(`post`.`content`) AS `contents` FROM `post` GROUP BY `post`.`userId`',
query: post.select(post.content.arrayAgg()).group(post.userId),
pg: {
text : 'SELECT array_agg("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT array_agg("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"'
},
sqlite: {
text : 'SELECT GROUP_CONCAT("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT GROUP_CONCAT("post"."content") AS "contents" FROM "post" GROUP BY "post"."userId"'
},
mysql: {
text : 'SELECT GROUP_CONCAT(`post`.`content`) AS `contents` FROM `post` GROUP BY `post`.`userId`',
string: 'SELECT GROUP_CONCAT(`post`.`content`) AS `contents` FROM `post` GROUP BY `post`.`userId`'
},
params: []

@@ -34,6 +61,15 @@ });

Harness.test({
query : post.select(post.content.arrayAgg('post contents')).group(post.userId),
pg : 'SELECT array_agg("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"',
sqlite: 'SELECT GROUP_CONCAT("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"',
mysql : 'SELECT GROUP_CONCAT(`post`.`content`) AS `post contents` FROM `post` GROUP BY `post`.`userId`',
query: post.select(post.content.arrayAgg('post contents')).group(post.userId),
pg: {
text : 'SELECT array_agg("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT array_agg("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"'
},
sqlite: {
text : 'SELECT GROUP_CONCAT("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"',
string: 'SELECT GROUP_CONCAT("post"."content") AS "post contents" FROM "post" GROUP BY "post"."userId"'
},
mysql: {
text : 'SELECT GROUP_CONCAT(`post`.`content`) AS `post contents` FROM `post` GROUP BY `post`.`userId`',
string: 'SELECT GROUP_CONCAT(`post`.`content`) AS `post contents` FROM `post` GROUP BY `post`.`userId`'
},
params: []

@@ -43,7 +79,16 @@ });

Harness.test({
query : post.select(post.content).group([post.userId, post.id]),
pg : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
sqlite: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
mysql : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`',
query: post.select(post.content).group([post.userId, post.id]),
pg: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"',
string: 'SELECT "post"."content" FROM "post" GROUP BY "post"."userId", "post"."id"'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`',
string: 'SELECT `post`.`content` FROM `post` GROUP BY `post`.`userId`, `post`.`id`'
},
params: []
});

45

test/dialects/having-tests.js

@@ -8,5 +8,14 @@ 'use strict';

query : post.select(post.userId, post.content.count()).group(post.userId).having(post.userId.gt(10)),
pg : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1)',
sqlite: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1)',
mysql : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?)',
pg : {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10)'
},
sqlite: {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10)'
},
mysql : {
text : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?)',
string: 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > 10)'
},
params: [10]

@@ -17,5 +26,14 @@ });

query : post.select(post.userId, post.content.count()).group(post.userId).having(post.userId.gt(10), post.userId.lt(100)),
pg : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
sqlite: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
mysql : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?) AND (`post`.`userId` < ?)',
pg : {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10) AND ("post"."userId" < 100)'
},
sqlite: {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10) AND ("post"."userId" < 100)'
},
mysql : {
text : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?) AND (`post`.`userId` < ?)',
string: 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > 10) AND (`post`.`userId` < 100)'
},
params: [10, 100]

@@ -26,6 +44,15 @@ });

query : post.select(post.userId, post.content.count()).group(post.userId).having([post.userId.gt(10), post.userId.lt(100)]),
pg : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
sqlite: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
mysql : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?) AND (`post`.`userId` < ?)',
pg : {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10) AND ("post"."userId" < 100)'
},
sqlite: {
text : 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > $1) AND ("post"."userId" < $2)',
string: 'SELECT "post"."userId", COUNT("post"."content") AS "content_count" FROM "post" GROUP BY "post"."userId" HAVING ("post"."userId" > 10) AND ("post"."userId" < 100)'
},
mysql : {
text : 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > ?) AND (`post`.`userId` < ?)',
string: 'SELECT `post`.`userId`, COUNT(`post`.`content`) AS `content_count` FROM `post` GROUP BY `post`.`userId` HAVING (`post`.`userId` > 10) AND (`post`.`userId` < 100)'
},
params: [10, 100]
});

161

test/dialects/indexes-tests.js

@@ -7,62 +7,151 @@ 'use strict';

Harness.test({
query: post.indexes(),
pg: "SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname=\"post\" AND pg_class.oid=pg_index.indrelid)",
mysql: "SHOW INDEX FROM `post`",
sqlite: "PRAGMA INDEX_LIST(\"post\")"
query: post.indexes(),
pg: {
text : 'SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname="post" AND pg_class.oid=pg_index.indrelid)',
string: 'SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname="post" AND pg_class.oid=pg_index.indrelid)'
},
mysql: {
text : 'SHOW INDEX FROM `post`',
string: 'SHOW INDEX FROM `post`'
},
sqlite: {
text : 'PRAGMA INDEX_LIST("post")',
string: 'PRAGMA INDEX_LIST("post")'
},
params: []
});
Harness.test({
query: post.indexes().create('index_name').unique().using('btree').on(post.id, post.userId).withParser('foo'),
pg: "CREATE UNIQUE INDEX \"index_name\" USING BTREE ON \"post\" (\"id\",\"userId\") WITH PARSER foo",
mysql: "CREATE UNIQUE INDEX `index_name` USING BTREE ON `post` (`id`,`userId`) WITH PARSER foo",
sqlite: "CREATE UNIQUE INDEX \"index_name\" USING BTREE ON \"post\" (\"id\",\"userId\") WITH PARSER foo"
query: post.indexes().create('index_name').unique().using('btree').on(post.id, post.userId).withParser('foo'),
pg: {
text : 'CREATE UNIQUE INDEX "index_name" USING BTREE ON "post" ("id","userId") WITH PARSER foo',
string: 'CREATE UNIQUE INDEX "index_name" USING BTREE ON "post" ("id","userId") WITH PARSER foo'
},
mysql: {
text : 'CREATE UNIQUE INDEX `index_name` USING BTREE ON `post` (`id`,`userId`) WITH PARSER foo',
string: 'CREATE UNIQUE INDEX `index_name` USING BTREE ON `post` (`id`,`userId`) WITH PARSER foo'
},
sqlite: {
text : 'CREATE UNIQUE INDEX "index_name" USING BTREE ON "post" ("id","userId") WITH PARSER foo',
string: 'CREATE UNIQUE INDEX "index_name" USING BTREE ON "post" ("id","userId") WITH PARSER foo'
},
params: []
});
Harness.test({
query: post.indexes().create().fulltext().on(post.id),
pg: "CREATE FULLTEXT INDEX \"post_id\" ON \"post\" (\"id\")",
mysql: "CREATE FULLTEXT INDEX `post_id` ON `post` (`id`)",
sqlite: "CREATE FULLTEXT INDEX \"post_id\" ON \"post\" (\"id\")"
query: post.indexes().create().fulltext().on(post.id),
pg: {
text : 'CREATE FULLTEXT INDEX "post_id" ON "post" ("id")',
string: 'CREATE FULLTEXT INDEX "post_id" ON "post" ("id")'
},
mysql: {
text : 'CREATE FULLTEXT INDEX `post_id` ON `post` (`id`)',
string: 'CREATE FULLTEXT INDEX `post_id` ON `post` (`id`)'
},
sqlite: {
text : 'CREATE FULLTEXT INDEX "post_id" ON "post" ("id")',
string: 'CREATE FULLTEXT INDEX "post_id" ON "post" ("id")'
},
params: []
});
Harness.test({
query: post.indexes().create().spatial().on(post.id),
pg: "CREATE SPATIAL INDEX \"post_id\" ON \"post\" (\"id\")",
mysql: "CREATE SPATIAL INDEX `post_id` ON `post` (`id`)",
sqlite: "CREATE SPATIAL INDEX \"post_id\" ON \"post\" (\"id\")"
query: post.indexes().create().spatial().on(post.id),
pg: {
text : 'CREATE SPATIAL INDEX "post_id" ON "post" ("id")',
string: 'CREATE SPATIAL INDEX "post_id" ON "post" ("id")'
},
mysql: {
text : 'CREATE SPATIAL INDEX `post_id` ON `post` (`id`)',
string: 'CREATE SPATIAL INDEX `post_id` ON `post` (`id`)'
},
sqlite: {
text : 'CREATE SPATIAL INDEX "post_id" ON "post" ("id")',
string: 'CREATE SPATIAL INDEX "post_id" ON "post" ("id")'
},
params: []
});
Harness.test({
query: post.indexes().create().on(post.userId, post.id),
pg: "CREATE INDEX \"post_id_userId\" ON \"post\" (\"userId\",\"id\")",
mysql: "CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)",
sqlite: "CREATE INDEX \"post_id_userId\" ON \"post\" (\"userId\",\"id\")"
query: post.indexes().create().on(post.userId, post.id),
pg: {
text : 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")',
string: 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")'
},
mysql: {
text : 'CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)',
string: 'CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)'
},
sqlite: {
text : 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")',
string: 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")'
},
params: []
});
Harness.test({
query: post.indexes().create().on(post.userId).on(post.id),
pg: "CREATE INDEX \"post_id_userId\" ON \"post\" (\"userId\",\"id\")",
mysql: "CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)",
sqlite: "CREATE INDEX \"post_id_userId\" ON \"post\" (\"userId\",\"id\")"
query: post.indexes().create().on(post.userId).on(post.id),
pg: {
text : 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")',
string: 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")'
},
mysql: {
text : 'CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)',
string: 'CREATE INDEX `post_id_userId` ON `post` (`userId`,`id`)'
},
sqlite: {
text : 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")',
string: 'CREATE INDEX "post_id_userId" ON "post" ("userId","id")'
},
params: []
});
Harness.test({
query: post.indexes().create(),
pg: { text: 'No columns defined!', throws: true },
mysql: { text: 'No columns defined!', throws: true },
sqlite: { text: 'No columns defined!', throws: true }
query: post.indexes().create(),
pg: {
text : 'No columns defined!',
throws: true
},
mysql: {
text : 'No columns defined!',
throws: true
},
sqlite: {
text : 'No columns defined!',
throws: true
}
});
Harness.test({
query: post.indexes().drop('index_name'),
pg: "DROP INDEX \"index_name\" ON \"post\"",
mysql: "DROP INDEX `index_name` ON `post`",
sqlite: "DROP INDEX \"index_name\" ON \"post\""
query: post.indexes().drop('index_name'),
pg: {
text : 'DROP INDEX "index_name" ON "post"',
string: 'DROP INDEX "index_name" ON "post"'
},
mysql: {
text : 'DROP INDEX `index_name` ON `post`',
string: 'DROP INDEX `index_name` ON `post`'
},
sqlite: {
text : 'DROP INDEX "index_name" ON "post"',
string: 'DROP INDEX "index_name" ON "post"'
},
params: []
});
Harness.test({
query: post.indexes().drop(post.userId, post.id),
pg: "DROP INDEX \"post_id_userId\" ON \"post\"",
mysql: "DROP INDEX `post_id_userId` ON `post`",
sqlite: "DROP INDEX \"post_id_userId\" ON \"post\""
query: post.indexes().drop(post.userId, post.id),
pg: {
text : 'DROP INDEX "post_id_userId" ON "post"',
string: 'DROP INDEX "post_id_userId" ON "post"'
},
mysql: {
text : 'DROP INDEX `post_id_userId` ON `post`',
string: 'DROP INDEX `post_id_userId` ON `post`'
},
sqlite: {
text : 'DROP INDEX "post_id_userId" ON "post"',
string: 'DROP INDEX "post_id_userId" ON "post"'
},
params: []
});

288

test/dialects/insert-tests.js

@@ -7,6 +7,15 @@ 'use strict';

Harness.test({
query : post.insert(post.content.value('test'), post.userId.value(1)),
pg : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
sqlite: 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
mysql : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?)',
query: post.insert(post.content.value('test'), post.userId.value(1)),
pg: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 1)'
},
sqlite: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 1)'
},
mysql: {
text : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?)',
string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'test\', 1)'
},
params: ['test', 1]

@@ -17,6 +26,15 @@ });

Harness.test({
query : post.insert(post.content.value('whoah')),
pg : 'INSERT INTO "post" ("content") VALUES ($1)',
sqlite: 'INSERT INTO "post" ("content") VALUES ($1)',
mysql : 'INSERT INTO `post` (`content`) VALUES (?)',
query: post.insert(post.content.value('whoah')),
pg: {
text : 'INSERT INTO "post" ("content") VALUES ($1)',
string: 'INSERT INTO "post" ("content") VALUES (\'whoah\')'
},
sqlite: {
text : 'INSERT INTO "post" ("content") VALUES ($1)',
string: 'INSERT INTO "post" ("content") VALUES (\'whoah\')'
},
mysql: {
text : 'INSERT INTO `post` (`content`) VALUES (?)',
string: 'INSERT INTO `post` (`content`) VALUES (\'whoah\')'
},
params: ['whoah']

@@ -26,6 +44,18 @@ });

Harness.test({
query : post.insert({content: 'test', userId: 2}),
pg : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
sqlite: 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
mysql : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?)',
query: post.insert({
content: 'test',
userId: 2
}),
pg: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2)'
},
sqlite: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'test\', 2)'
},
mysql: {
text : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?)',
string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'test\', 2)'
},
params: ['test', 2]

@@ -36,6 +66,20 @@ });

Harness.test({
query : post.insert([{content: 'whoah'}, {content: 'hey'}]),
pg : 'INSERT INTO "post" ("content") VALUES ($1), ($2)',
sqlite: 'INSERT INTO "post" ("content") VALUES ($1), ($2)',
mysql : 'INSERT INTO `post` (`content`) VALUES (?), (?)',
query: post.insert([{
content: 'whoah'
}, {
content: 'hey'
}
]),
pg: {
text : 'INSERT INTO "post" ("content") VALUES ($1), ($2)',
string: 'INSERT INTO "post" ("content") VALUES (\'whoah\'), (\'hey\')'
},
sqlite: {
text : 'INSERT INTO "post" ("content") VALUES ($1), ($2)',
string: 'INSERT INTO "post" ("content") VALUES (\'whoah\'), (\'hey\')'
},
mysql: {
text : 'INSERT INTO `post` (`content`) VALUES (?), (?)',
string: 'INSERT INTO `post` (`content`) VALUES (\'whoah\'), (\'hey\')'
},
params: ['whoah', 'hey']

@@ -45,6 +89,22 @@ });

Harness.test({
query : post.insert([{content: 'whoah', userId: 1}, {content: 'hey', userId: 2}]),
pg : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
sqlite: 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
mysql : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, ?)',
query: post.insert([{
content: 'whoah',
userId: 1
}, {
content: 'hey',
userId: 2
}
]),
pg: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'whoah\', 1), (\'hey\', 2)'
},
sqlite: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'whoah\', 1), (\'hey\', 2)'
},
mysql: {
text : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, ?)',
string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'whoah\', 1), (\'hey\', 2)'
},
params: ['whoah', 1, 'hey', 2]

@@ -55,47 +115,73 @@ });

Harness.test({
query : post.insert([{content: 'whoah', userId: 1}, {userId: 2, content: 'hey' }]),
pg : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
sqlite: 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
mysql : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, ?)',
query: post.insert([{
content: 'whoah',
userId: 1
}, {
userId: 2,
content: 'hey'
}
]),
pg: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'whoah\', 1), (\'hey\', 2)'
},
sqlite: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'whoah\', 1), (\'hey\', 2)'
},
mysql: {
text : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, ?)',
string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'whoah\', 1), (\'hey\', 2)'
},
params: ['whoah', 1, 'hey', 2]
});
// handle missing columns
Harness.test({
query : post.insert([{content: 'whoah', userId: 1}, {content: 'hey'}]),
pg : {
text: 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, $4)',
params: ['whoah', 1, 'hey', 'DEFAULT']
query: post.insert({}),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES',
string: 'INSERT INTO "post" DEFAULT VALUES'
},
sqlite: {
text: 'Sqlite requires the same number of columns in each insert row',
throws: true
text : 'INSERT INTO "post" DEFAULT VALUES',
string: 'INSERT INTO "post" DEFAULT VALUES'
},
mysql : {
text: 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, DEFAULT)',
params: ['whoah', 1, 'hey']
}
mysql: {
text : 'INSERT INTO `post` () VALUES ()',
string: 'INSERT INTO `post` () VALUES ()'
},
params: []
});
Harness.test({
query : post.insert([{userId: 1}, {content: 'hey', userId: 2}]),
pg : {
text: 'INSERT INTO "post" ("userId", "content") VALUES ($1, $2), ($3, $4)',
params: [1, 'DEFAULT', 2, 'hey']
query: post.insert({}).returning('*'),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING *'
},
sqlite: {
text: 'Sqlite requires the same number of columns in each insert row',
throws: true
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING *'
},
mysql : {
text: 'INSERT INTO `post` (`userId`, `content`) VALUES (?, DEFAULT), (?, ?)',
params: [1, 2, 'hey']
}
mysql: {
text : 'INSERT INTO `post` () VALUES () RETURNING *',
string: 'INSERT INTO `post` () VALUES () RETURNING *'
},
params: []
});
Harness.test({
query : post.insert({}),
pg : 'INSERT INTO "post" DEFAULT VALUES',
sqlite: 'INSERT INTO "post" DEFAULT VALUES',
mysql : 'INSERT INTO `post` () VALUES ()',
query: post.insert({}).returning(post.star()),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING *'
},
sqlite: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING *'
},
mysql: {
text : 'INSERT INTO `post` () VALUES () RETURNING *',
string: 'INSERT INTO `post` () VALUES () RETURNING *'
},
params: []

@@ -105,6 +191,15 @@ });

Harness.test({
query : post.insert({}).returning('*'),
pg : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
sqlite: 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
mysql : 'INSERT INTO `post` () VALUES () RETURNING *',
query: post.insert({}).returning(post.id),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"'
},
sqlite: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"'
},
mysql: {
text : 'INSERT INTO `post` () VALUES () RETURNING `id`',
string: 'INSERT INTO `post` () VALUES () RETURNING `id`'
},
params: []

@@ -114,6 +209,15 @@ });

Harness.test({
query : post.insert({}).returning(post.star()),
pg : 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
sqlite: 'INSERT INTO "post" DEFAULT VALUES RETURNING *',
mysql : 'INSERT INTO `post` () VALUES () RETURNING *',
query: post.insert({}).returning(post.id, post.content),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"'
},
sqlite: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"'
},
mysql: {
text : 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`',
string: 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`'
},
params: []

@@ -123,23 +227,65 @@ });

Harness.test({
query : post.insert({}).returning(post.id),
pg : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"',
sqlite: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id"',
mysql : 'INSERT INTO `post` () VALUES () RETURNING `id`',
query: post.insert({}).returning([post.id, post.content]),
pg: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"'
},
sqlite: {
text : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
string: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"'
},
mysql: {
text : 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`',
string: 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`'
},
params: []
});
// handle missing columns
Harness.test({
query : post.insert({}).returning(post.id, post.content),
pg : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
sqlite: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
mysql : 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`',
params: []
query: post.insert([{
content: 'whoah',
userId: 1
}, {
content: 'hey'
}
]),
pg: {
text : 'INSERT INTO "post" ("content", "userId") VALUES ($1, $2), ($3, DEFAULT)',
string: 'INSERT INTO "post" ("content", "userId") VALUES (\'whoah\', 1), (\'hey\', DEFAULT)',
params: ['whoah', 1, 'hey']
},
sqlite: {
text : 'Sqlite requires the same number of columns in each insert row',
throws: true
},
mysql: {
text : 'INSERT INTO `post` (`content`, `userId`) VALUES (?, ?), (?, DEFAULT)',
string: 'INSERT INTO `post` (`content`, `userId`) VALUES (\'whoah\', 1), (\'hey\', DEFAULT)',
params: ['whoah', 1, 'hey']
}
});
Harness.test({
query : post.insert({}).returning([post.id, post.content]),
pg : 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
sqlite: 'INSERT INTO "post" DEFAULT VALUES RETURNING "id", "content"',
mysql : 'INSERT INTO `post` () VALUES () RETURNING `id`, `content`',
params: []
query: post.insert([{
userId: 1
}, {
content: 'hey',
userId: 2
}
]),
pg: {
text : 'INSERT INTO "post" ("userId", "content") VALUES ($1, DEFAULT), ($2, $3)',
string: 'INSERT INTO "post" ("userId", "content") VALUES (1, DEFAULT), (2, \'hey\')',
params: [1, 2, 'hey']
},
sqlite: {
text : 'Sqlite requires the same number of columns in each insert row',
throws: true
},
mysql: {
text : 'INSERT INTO `post` (`userId`, `content`) VALUES (?, DEFAULT), (?, ?)',
string: 'INSERT INTO `post` (`userId`, `content`) VALUES (1, DEFAULT), (2, \'hey\')',
params: [1, 2, 'hey']
}
});

140

test/dialects/join-tests.js

@@ -9,52 +9,94 @@ 'use strict';

Harness.test({
query : user.select(user.name, post.content).from(user.join(post).on(user.id.equals(post.userId))),
pg : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
query: user.select(user.name, post.content).from(user.join(post).on(user.id.equals(post.userId))),
pg: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
},
sqlite: {
text : 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)',
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
},
params: []
});
Harness.test({
query : user.join(post).on(user.id.equals(post.userId)),
pg : '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
sqlite: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
mysql : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
query: user.join(post).on(user.id.equals(post.userId)),
pg: {
text : '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
},
sqlite: {
text : '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")',
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
},
mysql: {
text : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)',
string: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)'
},
params: []
});
Harness.test({
query : user
.select(user.name, post.content, comment.text)
.from(
user
.join(post).on(user.id.equals(post.userId))
.join(comment).on(post.id.equals(comment.postId))
),
pg : 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")' +
' INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
sqlite: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")' +
' INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
mysql : 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`)' +
' INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
query: user
.select(user.name, post.content, comment.text)
.from(
user
.join(post).on(user.id.equals(post.userId))
.join(comment).on(post.id.equals(comment.postId))),
pg: {
text : 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
string: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")'
},
sqlite: {
text : 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")',
string: 'SELECT "user"."name", "post"."content", "comment"."text" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId") INNER JOIN "comment" ON ("post"."id" = "comment"."postId")'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)',
string: 'SELECT `user`.`name`, `post`.`content`, `comment`.`text` FROM `user` INNER JOIN `post` ON (`user`.`id` = `post`.`userId`) INNER JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
},
params: []
});
Harness.test({
query : user.select(user.name, post.content).from(user.leftJoin(post).on(user.id.equals(post.userId))),
pg : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)'
query: user.select(user.name, post.content).from(user.leftJoin(post).on(user.id.equals(post.userId))),
pg: {
text : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")'
},
sqlite: {
text : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)',
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)'
},
params: []
});
Harness.test({
query : user
.select(user.name, post.content)
.from(
user
.leftJoin(post).on(user.id.equals(post.userId))
.leftJoin(comment).on(post.id.equals(comment.postId))
),
pg : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")' +
' LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
sqlite: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId")' +
' LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
mysql : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`)' +
' LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
query: user
.select(user.name, post.content)
.from(
user
.leftJoin(post).on(user.id.equals(post.userId))
.leftJoin(comment).on(post.id.equals(comment.postId))),
pg: {
text : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")'
},
sqlite: {
text : 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")',
string: 'SELECT "user"."name", "post"."content" FROM "user" LEFT JOIN "post" ON ("user"."id" = "post"."userId") LEFT JOIN "comment" ON ("post"."id" = "comment"."postId")'
},
mysql: {
text : 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`) LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)',
string: 'SELECT `user`.`name`, `post`.`content` FROM `user` LEFT JOIN `post` ON (`user`.`id` = `post`.`userId`) LEFT JOIN `comment` ON (`post`.`id` = `comment`.`postId`)'
},
params: []
});

@@ -65,14 +107,24 @@

.select(
post.content,
post.userId.as('subpostUserId'))
post.content,
post.userId.as('subpostUserId'))
.from(post);
Harness.test({
query : user
query: user
.select(user.name, subposts.content)
.from(user.join(subposts)
.on(user.id.equals(subposts.subpostUserId))),
pg : 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
sqlite: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
mysql : 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)'
pg: {
text : 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
string: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")'
},
sqlite: {
text : 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")',
string: 'SELECT "user"."name", "subposts"."content" FROM "user" INNER JOIN (SELECT "post"."content", "post"."userId" AS "subpostUserId" FROM "post") subposts ON ("user"."id" = "subposts"."subpostUserId")'
},
mysql: {
text : 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)',
string: 'SELECT `user`.`name`, `subposts`.`content` FROM `user` INNER JOIN (SELECT `post`.`content`, `post`.`userId` AS `subpostUserId` FROM `post`) subposts ON (`user`.`id` = `subposts`.`subpostUserId`)'
},
params: []
});

62

test/dialects/join-to-tests.js

@@ -9,3 +9,5 @@ 'use strict';

columns: {
id: { primaryKey: true }
id: {
primaryKey: true
}
}

@@ -26,3 +28,5 @@ });

columns: {
id: { primaryKey: true },
id: {
primaryKey: true
},
ownerId: {

@@ -38,20 +42,50 @@ references: {

Harness.test({
query : user.joinTo(post),
pg : '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
sqlite: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
mysql : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)'
query: user.joinTo(post),
pg: {
text : '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")'
},
sqlite: {
text : '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")',
string: '"user" INNER JOIN "post" ON ("user"."id" = "post"."ownerId")'
},
mysql: {
text : '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)',
string: '`user` INNER JOIN `post` ON (`user`.`id` = `post`.`ownerId`)'
},
params: []
});
Harness.test({
query : post.joinTo(user),
pg : '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
sqlite: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
mysql : '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)'
query: post.joinTo(user),
pg: {
text : '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
string: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")'
},
sqlite: {
text : '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")',
string: '"post" INNER JOIN "user" ON ("user"."id" = "post"."ownerId")'
},
mysql: {
text : '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)',
string: '`post` INNER JOIN `user` ON (`user`.`id` = `post`.`ownerId`)'
},
params: []
});
Harness.test({
query : user.joinTo(photo),
pg : '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
sqlite: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
mysql : '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)'
query: user.joinTo(photo),
pg: {
text : '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
string: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")'
},
sqlite: {
text : '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")',
string: '"user" INNER JOIN "photo" ON ("user"."id" = "photo"."ownerId")'
},
mysql: {
text : '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)',
string: '`user` INNER JOIN `photo` ON (`user`.`id` = `photo`.`ownerId`)'
},
params: []
});

75

test/dialects/limit-and-offset-tests.js

@@ -10,28 +10,71 @@ 'use strict';

Harness.test({
query : user.select(user.star()).from(user).order(user.name.asc).limit(1),
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1'
query: user.select(user.star()).from(user).order(user.name.asc).limit(1),
pg: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
},
sqlite: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
},
mysql: {
text : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1',
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1'
},
params: []
});
Harness.test({
query : user.select(user.star()).from(user).order(user.name.asc).limit(3).offset(6),
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6'
query: user.select(user.star()).from(user).order(user.name.asc).limit(3).offset(6),
pg: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
},
sqlite: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
},
mysql: {
text : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6',
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6'
},
params: []
});
Harness.test({
query : user.select(user.star()).from(user).order(user.name.asc).offset(10),
pg : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
sqlite: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
mysql : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10'
query: user.select(user.star()).from(user).order(user.name.asc).offset(10),
pg: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
},
sqlite: {
text : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
},
mysql: {
text : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10',
string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10'
},
params: []
});
Harness.test({
query : user.select(user.star()).where({name: 'John'}).offset(user.subQuery().select('FLOOR(RANDOM() * COUNT(*))').where({name: 'John'})).limit(1),
pg : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
sqlite: 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
mysql : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT 1',
query: user.select(user.star()).where({
name: 'John'
}).offset(user.subQuery().select('FLOOR(RANDOM() * COUNT(*))').where({
name: 'John'
})).limit(1),
pg: {
text : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
},
sqlite: {
text : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
},
mysql: {
text : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT 1',
string: 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = \'John\')) LIMIT 1'
},
values: ['John', 'John']
});

106

test/dialects/namespace-tests.js
'use strict';
var Harness = require('./support');
var Table = require(__dirname + '/../../lib/table');
var Table = require('../../lib/table');
var user = Harness.defineUserTable();

@@ -10,13 +10,33 @@ var post = Harness.definePostTable();

Harness.test({
query : u.select(u.name).from(u),
pg :'SELECT "u"."name" FROM "user" AS "u"',
sqlite:'SELECT "u"."name" FROM "user" AS "u"',
mysql :'SELECT `u`.`name` FROM `user` AS `u`'
query: u.select(u.name).from(u),
pg: {
text : 'SELECT "u"."name" FROM "user" AS "u"',
string: 'SELECT "u"."name" FROM "user" AS "u"'
},
sqlite: {
text : 'SELECT "u"."name" FROM "user" AS "u"',
string: 'SELECT "u"."name" FROM "user" AS "u"'
},
mysql: {
text : 'SELECT `u`.`name` FROM `user` AS `u`',
string: 'SELECT `u`.`name` FROM `user` AS `u`'
},
params: []
});
Harness.test({
query : u.select(u.star()).from(u),
pg : 'SELECT "u".* FROM "user" AS "u"',
sqlite: 'SELECT "u".* FROM "user" AS "u"',
mysql : 'SELECT `u`.* FROM `user` AS `u`'
query: u.select(u.star()).from(u),
pg: {
text : 'SELECT "u".* FROM "user" AS "u"',
string: 'SELECT "u".* FROM "user" AS "u"'
},
sqlite: {
text : 'SELECT "u".* FROM "user" AS "u"',
string: 'SELECT "u".* FROM "user" AS "u"'
},
mysql: {
text : 'SELECT `u`.* FROM `user` AS `u`',
string: 'SELECT `u`.* FROM `user` AS `u`'
},
params: []
});

@@ -26,14 +46,33 @@

Harness.test({
query : u.select(u.name).from(u.join(p).on(u.id.equals(p.userId).and(p.id.equals(3)))),
pg : 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = $1))',
sqlite: 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = $1))',
mysql : 'SELECT `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`id` = ?))',
params : [3]
query: u.select(u.name).from(u.join(p).on(u.id.equals(p.userId).and(p.id.equals(3)))),
pg: {
text : 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = $1))',
string: 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = 3))'
},
sqlite: {
text : 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = $1))',
string: 'SELECT "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."id" = 3))'
},
mysql: {
text : 'SELECT `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`id` = ?))',
string: 'SELECT `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`id` = 3))'
},
params: [3]
});
Harness.test({
query : u.select(p.content, u.name).from(u.join(p).on(u.id.equals(p.userId).and(p.content.isNotNull()))),
pg : 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))',
sqlite: 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))',
mysql : 'SELECT `p`.`content`, `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`content` IS NOT NULL))'
query: u.select(p.content, u.name).from(u.join(p).on(u.id.equals(p.userId).and(p.content.isNotNull()))),
pg: {
text : 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))',
string: 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))'
},
sqlite: {
text : 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))',
string: 'SELECT "p"."content", "u"."name" FROM "user" AS "u" INNER JOIN "post" AS "p" ON (("u"."id" = "p"."userId") AND ("p"."content" IS NOT NULL))'
},
mysql: {
text : 'SELECT `p`.`content`, `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`content` IS NOT NULL))',
string: 'SELECT `p`.`content`, `u`.`name` FROM `user` AS `u` INNER JOIN `post` AS `p` ON ((`u`.`id` = `p`.`userId`) AND (`p`.`content` IS NOT NULL))'
},
params: []
});

@@ -45,15 +84,26 @@

columns: [{
name: 'text',
quote: true
}, {
name: 'userId',
quote: false
}]
name: 'text',
quote: true
}, {
name: 'userId',
quote: false
}
]
});
Harness.test({
query : comment.select(comment.text, comment.userId),
pg : 'SELECT "comment"."text", "comment"."userId" FROM "comment"',
sqlite: 'SELECT "comment"."text", "comment"."userId" FROM "comment"',
mysql : 'SELECT `comment`.`text`, `comment`.`userId` FROM `comment`'
query: comment.select(comment.text, comment.userId),
pg: {
text : 'SELECT "comment"."text", "comment"."userId" FROM "comment"',
string: 'SELECT "comment"."text", "comment"."userId" FROM "comment"'
},
sqlite: {
text : 'SELECT "comment"."text", "comment"."userId" FROM "comment"',
string: 'SELECT "comment"."text", "comment"."userId" FROM "comment"'
},
mysql: {
text : 'SELECT `comment`.`text`, `comment`.`userId` FROM `comment`',
string: 'SELECT `comment`.`text`, `comment`.`userId` FROM `comment`'
},
params: []
});

72

test/dialects/order-tests.js

@@ -7,27 +7,67 @@ 'use strict';

Harness.test({
query : post.select(post.content).order(post.content),
pg : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"',
sqlite: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"',
mysql : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`'
query: post.select(post.content).order(post.content),
pg: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content"'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`',
string: 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`'
},
params: []
});
Harness.test({
query : post.select(post.content).order(post.content, post.userId.descending),
pg : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
sqlite: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
mysql : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
query: post.select(post.content).order(post.content, post.userId.descending),
pg: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC',
string: 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
},
params: []
});
Harness.test({
query : post.select(post.content).order(post.content.asc, post.userId.desc),
pg : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
sqlite: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
mysql : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
query: post.select(post.content).order(post.content.asc, post.userId.desc),
pg: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC',
string: 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
},
params: []
});
Harness.test({
query : post.select(post.content).order([post.content, post.userId.descending]),
pg : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
sqlite: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
mysql : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
query: post.select(post.content).order([post.content, post.userId.descending]),
pg: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC',
string: 'SELECT "post"."content" FROM "post" ORDER BY "post"."content", "post"."userId" DESC'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC',
string: 'SELECT `post`.`content` FROM `post` ORDER BY `post`.`content`, `post`.`userId` DESC'
},
params: []
});

92

test/dialects/schema-tests.js

@@ -10,17 +10,37 @@ 'use strict';

quote: true,
columns: ['id','name']
columns: ['id', 'name']
});
Harness.test({
query : userWithSchema.select(userWithSchema.id).from(userWithSchema),
pg : 'SELECT "staging"."user"."id" FROM "staging"."user"',
sqlite: 'SELECT "staging"."user"."id" FROM "staging"."user"',
mysql : 'SELECT `staging`.`user`.`id` FROM `staging`.`user`'
query: userWithSchema.select(userWithSchema.id).from(userWithSchema),
pg: {
text : 'SELECT "staging"."user"."id" FROM "staging"."user"',
string: 'SELECT "staging"."user"."id" FROM "staging"."user"'
},
sqlite: {
text : 'SELECT "staging"."user"."id" FROM "staging"."user"',
string: 'SELECT "staging"."user"."id" FROM "staging"."user"'
},
mysql: {
text : 'SELECT `staging`.`user`.`id` FROM `staging`.`user`',
string: 'SELECT `staging`.`user`.`id` FROM `staging`.`user`'
},
params: []
});
Harness.test({
query : userWithSchema.select(userWithSchema.id, userWithSchema.name).from(userWithSchema),
pg : 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"',
sqlite: 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"',
mysql : 'SELECT `staging`.`user`.`id`, `staging`.`user`.`name` FROM `staging`.`user`'
query: userWithSchema.select(userWithSchema.id, userWithSchema.name).from(userWithSchema),
pg: {
text : 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"',
string: 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"'
},
sqlite: {
text : 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"',
string: 'SELECT "staging"."user"."id", "staging"."user"."name" FROM "staging"."user"'
},
mysql: {
text : 'SELECT `staging`.`user`.`id`, `staging`.`user`.`name` FROM `staging`.`user`',
string: 'SELECT `staging`.`user`.`id`, `staging`.`user`.`name` FROM `staging`.`user`'
},
params: []
});

@@ -30,6 +50,16 @@

Harness.test({
query : uws.select(uws.name).from(uws),
pg :'SELECT "uws"."name" FROM "staging"."user" AS "uws"',
sqlite:'SELECT "uws"."name" FROM "staging"."user" AS "uws"',
mysql :'SELECT `uws`.`name` FROM `staging`.`user` AS `uws`'
query: uws.select(uws.name).from(uws),
pg: {
text : 'SELECT "uws"."name" FROM "staging"."user" AS "uws"',
string: 'SELECT "uws"."name" FROM "staging"."user" AS "uws"'
},
sqlite: {
text : 'SELECT "uws"."name" FROM "staging"."user" AS "uws"',
string: 'SELECT "uws"."name" FROM "staging"."user" AS "uws"'
},
mysql: {
text : 'SELECT `uws`.`name` FROM `staging`.`user` AS `uws`',
string: 'SELECT `uws`.`name` FROM `staging`.`user` AS `uws`'
},
params: []
});

@@ -44,13 +74,33 @@

Harness.test({
query : userWithSchema.select(userWithSchema.name, postWithSchema.content).from(userWithSchema.join(postWithSchema).on(userWithSchema.id.equals(postWithSchema.userId))),
pg : 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")',
sqlite: 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")',
mysql : 'SELECT `staging`.`user`.`name`, `dev`.`post`.`content` FROM `staging`.`user` INNER JOIN `dev`.`post` ON (`staging`.`user`.`id` = `dev`.`post`.`userId`)'
query: userWithSchema.select(userWithSchema.name, postWithSchema.content).from(userWithSchema.join(postWithSchema).on(userWithSchema.id.equals(postWithSchema.userId))),
pg: {
text : 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")',
string: 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")'
},
sqlite: {
text : 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")',
string: 'SELECT "staging"."user"."name", "dev"."post"."content" FROM "staging"."user" INNER JOIN "dev"."post" ON ("staging"."user"."id" = "dev"."post"."userId")'
},
mysql: {
text : 'SELECT `staging`.`user`.`name`, `dev`.`post`.`content` FROM `staging`.`user` INNER JOIN `dev`.`post` ON (`staging`.`user`.`id` = `dev`.`post`.`userId`)',
string: 'SELECT `staging`.`user`.`name`, `dev`.`post`.`content` FROM `staging`.`user` INNER JOIN `dev`.`post` ON (`staging`.`user`.`id` = `dev`.`post`.`userId`)'
},
params: []
});
Harness.test({
query : uws.select(uws.name, postWithSchema.content).from(uws.join(postWithSchema).on(uws.id.equals(postWithSchema.userId))),
pg : 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")',
sqlite: 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")',
mysql : 'SELECT `uws`.`name`, `dev`.`post`.`content` FROM `staging`.`user` AS `uws` INNER JOIN `dev`.`post` ON (`uws`.`id` = `dev`.`post`.`userId`)'
query: uws.select(uws.name, postWithSchema.content).from(uws.join(postWithSchema).on(uws.id.equals(postWithSchema.userId))),
pg: {
text : 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")',
string: 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")'
},
sqlite: {
text : 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")',
string: 'SELECT "uws"."name", "dev"."post"."content" FROM "staging"."user" AS "uws" INNER JOIN "dev"."post" ON ("uws"."id" = "dev"."post"."userId")'
},
mysql: {
text : 'SELECT `uws`.`name`, `dev`.`post`.`content` FROM `staging`.`user` AS `uws` INNER JOIN `dev`.`post` ON (`uws`.`id` = `dev`.`post`.`userId`)',
string: 'SELECT `uws`.`name`, `dev`.`post`.`content` FROM `staging`.`user` AS `uws` INNER JOIN `dev`.`post` ON (`uws`.`id` = `dev`.`post`.`userId`)'
},
params: []
});

18

test/dialects/select-tests.js

@@ -7,6 +7,16 @@ 'use strict';

Harness.test({
query : post.select(post.id).select(post.content),
pg : 'SELECT "post"."id", "post"."content" FROM "post"',
sqlite: 'SELECT "post"."id", "post"."content" FROM "post"',
mysql : 'SELECT `post`.`id`, `post`.`content` FROM `post`'
query: post.select(post.id).select(post.content),
pg: {
text : 'SELECT "post"."id", "post"."content" FROM "post"',
string: 'SELECT "post"."id", "post"."content" FROM "post"'
},
sqlite: {
text : 'SELECT "post"."id", "post"."content" FROM "post"',
string: 'SELECT "post"."id", "post"."content" FROM "post"'
},
mysql: {
text : 'SELECT `post`.`id`, `post`.`content` FROM `post`',
string: 'SELECT `post`.`id`, `post`.`content` FROM `post`'
},
params: []
});

112

test/dialects/shortcut-tests.js

@@ -9,22 +9,50 @@ 'use strict';

Harness.test({
query : user,
pg : 'SELECT "user".* FROM "user"',
sqlite: 'SELECT "user".* FROM "user"',
mysql : 'SELECT `user`.* FROM `user`'
query: user,
pg: {
text : 'SELECT "user".* FROM "user"',
string: 'SELECT "user".* FROM "user"'
},
sqlite: {
text : 'SELECT "user".* FROM "user"',
string: 'SELECT "user".* FROM "user"'
},
mysql: {
text : 'SELECT `user`.* FROM `user`',
string: 'SELECT `user`.* FROM `user`'
},
params: []
});
Harness.test({
query : user.where(user.name.equals(3)),
pg : 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT * FROM `user` WHERE (`user`.`name` = ?)',
params : [3]
query: user.where(user.name.equals(3)),
pg: {
text : 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT * FROM "user" WHERE ("user"."name" = 3)'
},
sqlite: {
text : 'SELECT * FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT * FROM "user" WHERE ("user"."name" = 3)'
},
mysql: {
text : 'SELECT * FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT * FROM `user` WHERE (`user`.`name` = 3)'
},
params: [3]
});
Harness.test({
query : user.where(user.name.equals(3)).where(user.id.equals(1)),
pg : 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
sqlite: 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
mysql : 'SELECT * FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
params: [3,1]
query: user.where(user.name.equals(3)).where(user.id.equals(1)),
pg: {
text : 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
string: 'SELECT * FROM "user" WHERE (("user"."name" = 3) AND ("user"."id" = 1))'
},
sqlite: {
text : 'SELECT * FROM "user" WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
string: 'SELECT * FROM "user" WHERE (("user"."name" = 3) AND ("user"."id" = 1))'
},
mysql: {
text : 'SELECT * FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
string: 'SELECT * FROM `user` WHERE ((`user`.`name` = 3) AND (`user`.`id` = 1))'
},
params: [3, 1]
});

@@ -34,13 +62,32 @@

Harness.test({
query : post.select(post.content),
pg : 'SELECT "post"."content" FROM "post"',
sqlite: 'SELECT "post"."content" FROM "post"',
mysql : 'SELECT `post`.`content` FROM `post`'
query: post.select(post.content),
pg: {
text : 'SELECT "post"."content" FROM "post"',
string: 'SELECT "post"."content" FROM "post"'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post"',
string: 'SELECT "post"."content" FROM "post"'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post`',
string: 'SELECT `post`.`content` FROM `post`'
},
params: []
});
Harness.test({
query : post.select(post.content).where(post.userId.equals(1)),
pg : 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = $1)',
sqlite: 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = $1)',
mysql : 'SELECT `post`.`content` FROM `post` WHERE (`post`.`userId` = ?)',
query: post.select(post.content).where(post.userId.equals(1)),
pg: {
text : 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = $1)',
string: 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = 1)'
},
sqlite: {
text : 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = $1)',
string: 'SELECT "post"."content" FROM "post" WHERE ("post"."userId" = 1)'
},
mysql: {
text : 'SELECT `post`.`content` FROM `post` WHERE (`post`.`userId` = ?)',
string: 'SELECT `post`.`content` FROM `post` WHERE (`post`.`userId` = 1)'
},
params: [1]

@@ -50,7 +97,20 @@ });

Harness.test({
query : post.where(post.content.isNull()).or({content: ''}).and({userId: 1}),
pg : 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
sqlite: 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
mysql : 'SELECT * FROM `post` WHERE (((`post`.`content` IS NULL) OR (`post`.`content` = ?)) AND (`post`.`userId` = ?))',
query: post.where(post.content.isNull()).or({
content: ''
}).and({
userId: 1
}),
pg: {
text : 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
string: 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = \'\')) AND ("post"."userId" = 1))'
},
sqlite: {
text : 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = $1)) AND ("post"."userId" = $2))',
string: 'SELECT * FROM "post" WHERE ((("post"."content" IS NULL) OR ("post"."content" = \'\')) AND ("post"."userId" = 1))'
},
mysql: {
text : 'SELECT * FROM `post` WHERE (((`post`.`content` IS NULL) OR (`post`.`content` = ?)) AND (`post`.`userId` = ?))',
string: 'SELECT * FROM `post` WHERE (((`post`.`content` IS NULL) OR (`post`.`content` = \'\')) AND (`post`.`userId` = 1))'
},
params: ['', 1]
});

63

test/dialects/support.js

@@ -5,32 +5,29 @@ /* global test */

var Table = require(__dirname + '/../../lib/table');
var Table = require('../../lib/table');
// specify dialect classes
var dialects = {
pg : require('../../lib/dialect/postgres'),
sqlite: require('../../lib/dialect/sqlite'),
mysql : require('../../lib/dialect/mysql')
pg : require('../../lib/dialect/postgres'),
sqlite : require('../../lib/dialect/sqlite'),
mysql : require('../../lib/dialect/mysql')
};
module.exports = {
test: function(expected) {
test: function(expected) {
// for each dialect
Object.keys(dialects).forEach(function(dialect) {
if(expected[dialect]) {
var expectedObject = expected[dialect];
if (undefined !== expectedObject) {
var DialectClass = dialects[dialect];
var title = dialect+': '+(expected.title || expected[dialect].text || expected[dialect]);
var title = dialect + ': ' + (expected.title || expectedObject.text || expectedObject);
test(title, function() {
// check if this query is expected to throw
if(expected[dialect].throws) {
if (expectedObject.throws) {
assert.throws(function() {
new DialectClass().getQuery(expected.query);
});
} else {
// build query for dialect

@@ -40,32 +37,42 @@ var compiledQuery = new DialectClass().getQuery(expected.query);

// test result is correct
var expectedText = expected[dialect].text || expected[dialect];
assert.equal(compiledQuery.text, expectedText,'query result');
var expectedText = expectedObject.text || expectedObject;
assert.equal(compiledQuery.text, expectedText, 'query result');
// if params are specified then test these are correct
var expectedParams = expected[dialect].params || expected.params;
if(expectedParams) {
var expectedParams = expectedObject.params || expected.params;
if (undefined !== expectedParams) {
assert.equal(expectedParams.length, compiledQuery.values.length, 'params length');
for(var i = 0; i < expectedParams.length; i++) {
assert.equal(expectedParams[i], compiledQuery.values[i], 'param '+(i+1));
for (var i = 0; i < expectedParams.length; i++) {
assert.equal(expectedParams[i], compiledQuery.values[i], 'param ' + (i + 1));
}
}
}
if (undefined !== expectedObject.string) {
// test the toString
if (expectedObject.throws) {
assert.throws(function() {
new DialectClass().getString(expected.query);
});
} else {
var compiledString = new DialectClass().getString(expected.query);
// test result is correct
assert.equal(compiledString, expectedObject.string);
}
}
});
} // if
}); // forEach
},
defineUserTable: function () {
defineUserTable: function() {
return Table.define({
name: 'user',
quote: true,
columns: ['id','name']
columns: ['id', 'name']
});
},
definePostTable: function () {
definePostTable: function() {
return Table.define({

@@ -77,3 +84,3 @@ name: 'post',

defineCommentTable: function () {
defineCommentTable: function() {
return Table.define({

@@ -85,3 +92,3 @@ name: 'comment',

defineCustomerTable: function () {
defineCustomerTable: function() {
return Table.define({

@@ -96,6 +103,6 @@ name: 'customer',

return Table.define({
name: 'variable',
columns: ['a', 'b', 'c', 'd', 't', 'u', 'v', 'x', 'y', 'z']
name: 'variable',
columns: ['a', 'b', 'c', 'd', 't', 'u', 'v', 'x', 'y', 'z']
});
}
};

413

test/dialects/table-tests.js

@@ -7,27 +7,66 @@ 'use strict';

Harness.test({
query : user.select(user.id).from(user),
pg : 'SELECT "user"."id" FROM "user"',
sqlite: 'SELECT "user"."id" FROM "user"',
mysql : 'SELECT `user`.`id` FROM `user`'
query: user.select(user.id).from(user),
pg: {
text : 'SELECT "user"."id" FROM "user"',
string: 'SELECT "user"."id" FROM "user"'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user"',
string: 'SELECT "user"."id" FROM "user"'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user`',
string: 'SELECT `user`.`id` FROM `user`'
},
params: []
});
Harness.test({
query : user.select(user.id, user.name).from(user),
pg : 'SELECT "user"."id", "user"."name" FROM "user"',
sqlite: 'SELECT "user"."id", "user"."name" FROM "user"',
mysql : 'SELECT `user`.`id`, `user`.`name` FROM `user`'
query: user.select(user.id, user.name).from(user),
pg: {
text : 'SELECT "user"."id", "user"."name" FROM "user"',
string: 'SELECT "user"."id", "user"."name" FROM "user"'
},
sqlite: {
text : 'SELECT "user"."id", "user"."name" FROM "user"',
string: 'SELECT "user"."id", "user"."name" FROM "user"'
},
mysql: {
text : 'SELECT `user`.`id`, `user`.`name` FROM `user`',
string: 'SELECT `user`.`id`, `user`.`name` FROM `user`'
},
params: []
});
Harness.test({
query : user.select(user.star()).from(user),
pg : 'SELECT "user".* FROM "user"',
sqlite: 'SELECT "user".* FROM "user"',
mysql : 'SELECT `user`.* FROM `user`'
query: user.select(user.star()).from(user),
pg: {
text : 'SELECT "user".* FROM "user"',
string: 'SELECT "user".* FROM "user"'
},
sqlite: {
text : 'SELECT "user".* FROM "user"',
string: 'SELECT "user".* FROM "user"'
},
mysql: {
text : 'SELECT `user`.* FROM `user`',
string: 'SELECT `user`.* FROM `user`'
},
params: []
});
Harness.test({
query : user.select(user.id).from(user).where(user.name.equals('foo')),
pg : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
query: user.select(user.id).from(user).where(user.name.equals('foo')),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'foo\')'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" = \'foo\')'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` = \'foo\')'
},
params: ['foo']

@@ -37,6 +76,15 @@ });

Harness.test({
query : user.select(user.id).from(user).where(user.name.equals('foo').or(user.name.equals('bar'))),
pg : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) OR (`user`.`name` = ?))',
query: user.select(user.id).from(user).where(user.name.equals('foo').or(user.name.equals('bar'))),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') OR ("user"."name" = \'bar\'))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') OR ("user"."name" = \'bar\'))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) OR (`user`.`name` = ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = \'foo\') OR (`user`.`name` = \'bar\'))'
},
params: ['foo', 'bar']

@@ -46,6 +94,15 @@ });

Harness.test({
query : user.select(user.id).from(user).where(user.name.equals('foo').and(user.name.equals('bar'))),
pg : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) AND ("user"."name" = $2))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) AND ("user"."name" = $2))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`name` = ?))',
query: user.select(user.id).from(user).where(user.name.equals('foo').and(user.name.equals('bar'))),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) AND ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') AND ("user"."name" = \'bar\'))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) AND ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') AND ("user"."name" = \'bar\'))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) AND (`user`.`name` = ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = \'foo\') AND (`user`.`name` = \'bar\'))'
},
params: ['foo', 'bar']

@@ -55,20 +112,49 @@ });

Harness.test({
query : user.select(user.id).from(user).where(user.name.equals('foo')).or(user.name.equals('bar')),
pg : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) OR (`user`.`name` = ?))'
query: user.select(user.id).from(user).where(user.name.equals('foo')).or(user.name.equals('bar')),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') OR ("user"."name" = \'bar\'))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = $1) OR ("user"."name" = $2))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" = \'foo\') OR ("user"."name" = \'bar\'))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = ?) OR (`user`.`name` = ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` = \'foo\') OR (`user`.`name` = \'bar\'))'
},
params: ['foo', 'bar']
});
Harness.test({
query : user.select(user.id).from(user).where(user.name.equals('foo')).or(user.name.equals('baz')).and(user.name.equals('bar')),
pg : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) OR ("user"."name" = $2)) AND ("user"."name" = $3))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) OR ("user"."name" = $2)) AND ("user"."name" = $3))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = ?) OR (`user`.`name` = ?)) AND (`user`.`name` = ?))'
query: user.select(user.id).from(user).where(user.name.equals('foo')).or(user.name.equals('baz')).and(user.name.equals('bar')),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) OR ("user"."name" = $2)) AND ("user"."name" = $3))',
string: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = \'foo\') OR ("user"."name" = \'baz\')) AND ("user"."name" = \'bar\'))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) OR ("user"."name" = $2)) AND ("user"."name" = $3))',
string: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = \'foo\') OR ("user"."name" = \'baz\')) AND ("user"."name" = \'bar\'))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = ?) OR (`user`.`name` = ?)) AND (`user`.`name` = ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = \'foo\') OR (`user`.`name` = \'baz\')) AND (`user`.`name` = \'bar\'))'
},
params: ['foo', 'baz', 'bar']
});
Harness.test({
query : user.select(user.id).from(user).where(user.name['in'](['foo', 'bar'])),
pg : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN ($1, $2))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN ($1, $2))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` IN (?, ?))',
query: user.select(user.id).from(user).where(user.name.in(['foo', 'bar'])),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN ($1, $2))',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN (\'foo\', \'bar\'))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN ($1, $2))',
string: 'SELECT "user"."id" FROM "user" WHERE ("user"."name" IN (\'foo\', \'bar\'))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` IN (?, ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE (`user`.`name` IN (\'foo\', \'bar\'))'
},
params: ['foo', 'bar']

@@ -78,6 +164,15 @@ });

Harness.test({
query : user.select(user.id).from(user).where(user.name['in'](['foo', 'bar']).and(user.id.equals(1))),
pg : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN ($1, $2)) AND ("user"."id" = $3))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN ($1, $2)) AND ("user"."id" = $3))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` IN (?, ?)) AND (`user`.`id` = ?))',
query: user.select(user.id).from(user).where(user.name.in(['foo', 'bar']).and(user.id.equals(1))),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN ($1, $2)) AND ("user"."id" = $3))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN (\'foo\', \'bar\')) AND ("user"."id" = 1))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN ($1, $2)) AND ("user"."id" = $3))',
string: 'SELECT "user"."id" FROM "user" WHERE (("user"."name" IN (\'foo\', \'bar\')) AND ("user"."id" = 1))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` IN (?, ?)) AND (`user`.`id` = ?))',
string: 'SELECT `user`.`id` FROM `user` WHERE ((`user`.`name` IN (\'foo\', \'bar\')) AND (`user`.`id` = 1))'
},
params: ['foo', 'bar', 1]

@@ -87,6 +182,15 @@ });

Harness.test({
query : user.select(user.columns),
pg : 'SELECT "user"."id", "user"."name" FROM "user"',
sqlite: 'SELECT "user"."id", "user"."name" FROM "user"',
mysql : 'SELECT `user`.`id`, `user`.`name` FROM `user`',
query: user.select(user.columns),
pg: {
text : 'SELECT "user"."id", "user"."name" FROM "user"',
string: 'SELECT "user"."id", "user"."name" FROM "user"'
},
sqlite: {
text : 'SELECT "user"."id", "user"."name" FROM "user"',
string: 'SELECT "user"."id", "user"."name" FROM "user"'
},
mysql: {
text : 'SELECT `user`.`id`, `user`.`name` FROM `user`',
string: 'SELECT `user`.`id`, `user`.`name` FROM `user`'
},
params: []

@@ -97,29 +201,55 @@ });

Harness.test({
query : user
query: user
.select(user.id)
.from(user)
.where(
user.name.equals('boom')
.and(user.id.equals(1))
).or(
user.name.equals('bang').and(user.id.equals(2))
),
pg : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4)))',
sqlite: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4)))',
mysql : 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = ?) AND (`user`.`id` = ?)) OR ((`user`.`name` = ?) AND (`user`.`id` = ?)))',
params: ['boom', 1, 'bang', 2]
user.name.equals('boom')
.and(user.id.equals(1))).or(
user.name.equals('bang').and(user.id.equals(2))),
pg: {
text : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4)))',
string: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = \'boom\') AND ("user"."id" = 1)) OR (("user"."name" = \'bang\') AND ("user"."id" = 2)))'
},
sqlite: {
text : 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = $1) AND ("user"."id" = $2)) OR (("user"."name" = $3) AND ("user"."id" = $4)))',
string: 'SELECT "user"."id" FROM "user" WHERE ((("user"."name" = \'boom\') AND ("user"."id" = 1)) OR (("user"."name" = \'bang\') AND ("user"."id" = 2)))'
},
mysql: {
text : 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = ?) AND (`user`.`id` = ?)) OR ((`user`.`name` = ?) AND (`user`.`id` = ?)))',
string: 'SELECT `user`.`id` FROM `user` WHERE (((`user`.`name` = \'boom\') AND (`user`.`id` = 1)) OR ((`user`.`name` = \'bang\') AND (`user`.`id` = 2)))'
},
params: ['boom', 1, 'bang', 2]
});
Harness.test({
query : user.select(user.name.as('user name'), user.id.as('user id')).from(user),
pg : 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"',
sqlite: 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"',
mysql : 'SELECT `user`.`name` AS `user name`, `user`.`id` AS `user id` FROM `user`'
query: user.select(user.name.as('user name'), user.id.as('user id')).from(user),
pg: {
text : 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"',
string: 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"'
},
sqlite: {
text : 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"',
string: 'SELECT "user"."name" AS "user name", "user"."id" AS "user id" FROM "user"'
},
mysql: {
text : 'SELECT `user`.`name` AS `user name`, `user`.`id` AS `user id` FROM `user`',
string: 'SELECT `user`.`name` AS `user name`, `user`.`id` AS `user id` FROM `user`'
},
params: []
});
Harness.test({
query : user.select(user.name.as('user name')).from(user).where(user.name.equals('brian')),
pg : 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`name` AS `user name` FROM `user` WHERE (`user`.`name` = ?)',
query: user.select(user.name.as('user name')).from(user).where(user.name.equals('brian')),
pg: {
text : 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = \'brian\')'
},
sqlite: {
text : 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name" AS "user name" FROM "user" WHERE ("user"."name" = \'brian\')'
},
mysql: {
text : 'SELECT `user`.`name` AS `user name` FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`name` AS `user name` FROM `user` WHERE (`user`.`name` = \'brian\')'
},
params: ['brian']

@@ -129,13 +259,32 @@ });

Harness.test({
query : user.select(user.name).from(user).where(user.name.equals('brian')),
pg : 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = $1)',
sqlite: 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = $1)',
mysql : 'SELECT `user`.`name` FROM `user` WHERE (`user`.`name` = ?)'
query: user.select(user.name).from(user).where(user.name.equals('brian')),
pg: {
text : 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = \'brian\')'
},
sqlite: {
text : 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = $1)',
string: 'SELECT "user"."name" FROM "user" WHERE ("user"."name" = \'brian\')'
},
mysql: {
text : 'SELECT `user`.`name` FROM `user` WHERE (`user`.`name` = ?)',
string: 'SELECT `user`.`name` FROM `user` WHERE (`user`.`name` = \'brian\')'
},
params: ['brian']
});
Harness.test({
query : user.select('name').from('user').where('name <> NULL'),
pg : 'SELECT name FROM user WHERE (name <> NULL)',
sqlite: 'SELECT name FROM user WHERE (name <> NULL)',
mysql : 'SELECT name FROM user WHERE (name <> NULL)',
query: user.select('name').from('user').where('name <> NULL'),
pg: {
text : 'SELECT name FROM user WHERE (name <> NULL)',
string: 'SELECT name FROM user WHERE (name <> NULL)'
},
sqlite: {
text : 'SELECT name FROM user WHERE (name <> NULL)',
string: 'SELECT name FROM user WHERE (name <> NULL)'
},
mysql: {
text : 'SELECT name FROM user WHERE (name <> NULL)',
string: 'SELECT name FROM user WHERE (name <> NULL)'
},
params: []

@@ -145,6 +294,15 @@ });

Harness.test({
query : user.select('name,id').from('user').where('name <> NULL'),
pg : 'SELECT name,id FROM user WHERE (name <> NULL)',
sqlite: 'SELECT name,id FROM user WHERE (name <> NULL)',
mysql : 'SELECT name,id FROM user WHERE (name <> NULL)',
query: user.select('name,id').from('user').where('name <> NULL'),
pg: {
text : 'SELECT name,id FROM user WHERE (name <> NULL)',
string: 'SELECT name,id FROM user WHERE (name <> NULL)'
},
sqlite: {
text : 'SELECT name,id FROM user WHERE (name <> NULL)',
string: 'SELECT name,id FROM user WHERE (name <> NULL)'
},
mysql: {
text : 'SELECT name,id FROM user WHERE (name <> NULL)',
string: 'SELECT name,id FROM user WHERE (name <> NULL)'
},
params: []

@@ -154,6 +312,15 @@ });

Harness.test({
query : user.select('name','id').from('user').where('name <> NULL'),
pg : 'SELECT name, id FROM user WHERE (name <> NULL)',
sqlite: 'SELECT name, id FROM user WHERE (name <> NULL)',
mysql : 'SELECT name, id FROM user WHERE (name <> NULL)',
query: user.select('name', 'id').from('user').where('name <> NULL'),
pg: {
text : 'SELECT name, id FROM user WHERE (name <> NULL)',
string: 'SELECT name, id FROM user WHERE (name <> NULL)'
},
sqlite: {
text : 'SELECT name, id FROM user WHERE (name <> NULL)',
string: 'SELECT name, id FROM user WHERE (name <> NULL)'
},
mysql: {
text : 'SELECT name, id FROM user WHERE (name <> NULL)',
string: 'SELECT name, id FROM user WHERE (name <> NULL)'
},
params: []

@@ -163,6 +330,15 @@ });

Harness.test({
query : user.select('name','id').from('user').where('name <> NULL').and('id <> NULL'),
pg : 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
sqlite: 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
mysql : 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
query: user.select('name', 'id').from('user').where('name <> NULL').and('id <> NULL'),
pg: {
text : 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
string: 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))'
},
sqlite: {
text : 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
string: 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))'
},
mysql: {
text : 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))',
string: 'SELECT name, id FROM user WHERE ((name <> NULL) AND (id <> NULL))'
},
params: []

@@ -172,6 +348,17 @@ });

Harness.test({
query : user.select('name').from('user').where({name: 'brian'}),
pg : 'SELECT name FROM user WHERE ("user"."name" = $1)',
sqlite: 'SELECT name FROM user WHERE ("user"."name" = $1)',
mysql : 'SELECT name FROM user WHERE (`user`.`name` = ?)',
query: user.select('name').from('user').where({
name: 'brian'
}),
pg: {
text : 'SELECT name FROM user WHERE ("user"."name" = $1)',
string: 'SELECT name FROM user WHERE ("user"."name" = \'brian\')'
},
sqlite: {
text : 'SELECT name FROM user WHERE ("user"."name" = $1)',
string: 'SELECT name FROM user WHERE ("user"."name" = \'brian\')'
},
mysql: {
text : 'SELECT name FROM user WHERE (`user`.`name` = ?)',
string: 'SELECT name FROM user WHERE (`user`.`name` = \'brian\')'
},
params: ['brian']

@@ -181,6 +368,18 @@ });

Harness.test({
query : user.select('name').from('user').where({name: 'brian', id: 1}),
pg : 'SELECT name FROM user WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
sqlite: 'SELECT name FROM user WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
mysql : 'SELECT name FROM user WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
query: user.select('name').from('user').where({
name: 'brian',
id: 1
}),
pg: {
text : 'SELECT name FROM user WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
string: 'SELECT name FROM user WHERE (("user"."name" = \'brian\') AND ("user"."id" = 1))'
},
sqlite: {
text : 'SELECT name FROM user WHERE (("user"."name" = $1) AND ("user"."id" = $2))',
string: 'SELECT name FROM user WHERE (("user"."name" = \'brian\') AND ("user"."id" = 1))'
},
mysql: {
text : 'SELECT name FROM user WHERE ((`user`.`name` = ?) AND (`user`.`id` = ?))',
string: 'SELECT name FROM user WHERE ((`user`.`name` = \'brian\') AND (`user`.`id` = 1))'
},
params: ['brian', 1]

@@ -190,6 +389,15 @@ });

Harness.test({
query : user.select(user.name.as('quote"quote"tick`tick`')),
pg : 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"',
sqlite: 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"',
mysql : 'SELECT `user`.`name` AS `quote"quote"tick``tick``` FROM `user`',
query: user.select(user.name.as('quote"quote"tick`tick`')),
pg: {
text : 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"',
string: 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"'
},
sqlite: {
text : 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"',
string: 'SELECT "user"."name" AS "quote""quote""tick`tick`" FROM "user"'
},
mysql: {
text : 'SELECT `user`.`name` AS `quote"quote"tick``tick``` FROM `user`',
string: 'SELECT `user`.`name` AS `quote"quote"tick``tick``` FROM `user`'
},
params: []

@@ -199,7 +407,16 @@ });

Harness.test({
query : user.select(user.star()).where(user.id['in'](user.subQuery().select(user.id))),
pg : 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))',
sqlite: 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))',
mysql : 'SELECT `user`.* FROM `user` WHERE (`user`.`id` IN (SELECT `user`.`id` FROM `user`))',
query: user.select(user.star()).where(user.id.in(user.subQuery().select(user.id))),
pg: {
text : 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))',
string: 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))'
},
sqlite: {
text : 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))',
string: 'SELECT "user".* FROM "user" WHERE ("user"."id" IN (SELECT "user"."id" FROM "user"))'
},
mysql: {
text : 'SELECT `user`.* FROM `user` WHERE (`user`.`id` IN (SELECT `user`.`id` FROM `user`))',
string: 'SELECT `user`.* FROM `user` WHERE (`user`.`id` IN (SELECT `user`.`id` FROM `user`))'
},
params: []
});

34

test/dialects/ternary-clause-tests.js

@@ -9,6 +9,15 @@ 'use strict';

Harness.test({
query : customer.select().where(customer.age.between(18, 25)),
pg : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN $1 AND $2)',
sqlite: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN $1 AND $2)',
mysql : 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` BETWEEN ? AND ?)',
query: customer.select().where(customer.age.between(18, 25)),
pg: {
text : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN $1 AND $2)',
string: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN 18 AND 25)'
},
sqlite: {
text : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN $1 AND $2)',
string: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" BETWEEN 18 AND 25)'
},
mysql: {
text : 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` BETWEEN ? AND ?)',
string: 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` BETWEEN 18 AND 25)'
},
params: [18, 25]

@@ -18,7 +27,16 @@ });

Harness.test({
query : post.select().where(post.userId.between(customer.subQuery().select(customer.id.min()), customer.subQuery().select(customer.id.max()))),
pg : 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))',
sqlite: 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))',
mysql : 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` BETWEEN (SELECT MIN(`customer`.`id`) AS `id_min` FROM `customer`) AND (SELECT MAX(`customer`.`id`) AS `id_max` FROM `customer`))',
query: post.select().where(post.userId.between(customer.subQuery().select(customer.id.min()), customer.subQuery().select(customer.id.max()))),
pg: {
text : 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))',
string: 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))'
},
sqlite: {
text : 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))',
string: 'SELECT "post".* FROM "post" WHERE ("post"."userId" BETWEEN (SELECT MIN("customer"."id") AS "id_min" FROM "customer") AND (SELECT MAX("customer"."id") AS "id_max" FROM "customer"))'
},
mysql: {
text : 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` BETWEEN (SELECT MIN(`customer`.`id`) AS `id_min` FROM `customer`) AND (SELECT MAX(`customer`.`id`) AS `id_max` FROM `customer`))',
string: 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` BETWEEN (SELECT MIN(`customer`.`id`) AS `id_min` FROM `customer`) AND (SELECT MAX(`customer`.`id`) AS `id_max` FROM `customer`))'
},
params: []
});

34

test/dialects/unary-clause-tests.js

@@ -9,6 +9,15 @@ 'use strict';

Harness.test({
query : customer.select().where(customer.age.isNotNull()),
pg : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)',
sqlite: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)',
mysql : 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` IS NOT NULL)',
query: customer.select().where(customer.age.isNotNull()),
pg: {
text : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)',
string: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)'
},
sqlite: {
text : 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)',
string: 'SELECT "customer".* FROM "customer" WHERE ("customer"."age" IS NOT NULL)'
},
mysql: {
text : 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` IS NOT NULL)',
string: 'SELECT `customer`.* FROM `customer` WHERE (`customer`.`age` IS NOT NULL)'
},
params: []

@@ -18,7 +27,16 @@ });

Harness.test({
query : post.select().where(post.userId.in(customer.subQuery().select(customer.id).where(customer.age.isNull()))),
pg : 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))',
sqlite: 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))',
mysql : 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer` WHERE (`customer`.`age` IS NULL)))',
query: post.select().where(post.userId. in (customer.subQuery().select(customer.id).where(customer.age.isNull()))),
pg: {
text : 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))',
string: 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))'
},
sqlite: {
text : 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))',
string: 'SELECT "post".* FROM "post" WHERE ("post"."userId" IN (SELECT "customer"."id" FROM "customer" WHERE ("customer"."age" IS NULL)))'
},
mysql: {
text : 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer` WHERE (`customer`.`age` IS NULL)))',
string: 'SELECT `post`.* FROM `post` WHERE (`post`.`userId` IN (SELECT `customer`.`id` FROM `customer` WHERE (`customer`.`age` IS NULL)))'
},
params: []
});

117

test/dialects/update-tests.js

@@ -8,6 +8,17 @@ 'use strict';

Harness.test({
query : post.update({content: 'test'}),
pg : 'UPDATE "post" SET "content" = $1',
sqlite: 'UPDATE "post" SET "content" = $1',
mysql : 'UPDATE `post` SET `content` = ?',
query: post.update({
content: 'test'
}),
pg: {
text : 'UPDATE "post" SET "content" = $1',
string: 'UPDATE "post" SET "content" = \'test\''
},
sqlite: {
text : 'UPDATE "post" SET "content" = $1',
string: 'UPDATE "post" SET "content" = \'test\''
},
mysql: {
text : 'UPDATE `post` SET `content` = ?',
string: 'UPDATE `post` SET `content` = \'test\''
},
params: ['test']

@@ -17,6 +28,18 @@ });

Harness.test({
query : post.update({content: 'test', userId: 3}),
pg : 'UPDATE "post" SET "content" = $1, "userId" = $2',
sqlite: 'UPDATE "post" SET "content" = $1, "userId" = $2',
mysql : 'UPDATE `post` SET `content` = ?, `userId` = ?',
query: post.update({
content: 'test',
userId: 3
}),
pg: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2',
string: 'UPDATE "post" SET "content" = \'test\', "userId" = 3'
},
sqlite: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2',
string: 'UPDATE "post" SET "content" = \'test\', "userId" = 3'
},
mysql: {
text : 'UPDATE `post` SET `content` = ?, `userId` = ?',
string: 'UPDATE `post` SET `content` = \'test\', `userId` = 3'
},
params: ['test', 3]

@@ -26,6 +49,18 @@ });

Harness.test({
query : post.update({content: null, userId: 3}),
pg : 'UPDATE "post" SET "content" = $1, "userId" = $2',
sqlite: 'UPDATE "post" SET "content" = $1, "userId" = $2',
mysql : 'UPDATE `post` SET `content` = ?, `userId` = ?',
query: post.update({
content: null,
userId: 3
}),
pg: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2',
string: 'UPDATE "post" SET "content" = NULL, "userId" = 3'
},
sqlite: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2',
string: 'UPDATE "post" SET "content" = NULL, "userId" = 3'
},
mysql: {
text : 'UPDATE `post` SET `content` = ?, `userId` = ?',
string: 'UPDATE `post` SET `content` = NULL, `userId` = 3'
},
params: [null, 3]

@@ -35,6 +70,18 @@ });

Harness.test({
query : post.update({content: 'test', userId: 3}).where(post.content.equals('no')),
pg : 'UPDATE "post" SET "content" = $1, "userId" = $2 WHERE ("post"."content" = $3)',
sqlite: 'UPDATE "post" SET "content" = $1, "userId" = $2 WHERE ("post"."content" = $3)',
mysql : 'UPDATE `post` SET `content` = ?, `userId` = ? WHERE (`post`.`content` = ?)',
query: post.update({
content: 'test',
userId: 3
}).where(post.content.equals('no')),
pg: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2 WHERE ("post"."content" = $3)',
string: 'UPDATE "post" SET "content" = \'test\', "userId" = 3 WHERE ("post"."content" = \'no\')'
},
sqlite: {
text : 'UPDATE "post" SET "content" = $1, "userId" = $2 WHERE ("post"."content" = $3)',
string: 'UPDATE "post" SET "content" = \'test\', "userId" = 3 WHERE ("post"."content" = \'no\')'
},
mysql: {
text : 'UPDATE `post` SET `content` = ?, `userId` = ? WHERE (`post`.`content` = ?)',
string: 'UPDATE `post` SET `content` = \'test\', `userId` = 3 WHERE (`post`.`content` = \'no\')'
},
params: ['test', 3, 'no']

@@ -44,6 +91,17 @@ });

Harness.test({
query : post.update({content: user.name}).from(user).where(post.userId.equals(user.id)),
sqlite: 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")',
pg : 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")',
mysql : 'UPDATE `post` SET `content` = `user`.`name` FROM `user` WHERE (`post`.`userId` = `user`.`id`)',
query: post.update({
content: user.name
}).from(user).where(post.userId.equals(user.id)),
sqlite: {
text : 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")',
string: 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")'
},
pg: {
text : 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")',
string: 'UPDATE "post" SET "content" = "user"."name" FROM "user" WHERE ("post"."userId" = "user"."id")'
},
mysql: {
text : 'UPDATE `post` SET `content` = `user`.`name` FROM `user` WHERE (`post`.`userId` = `user`.`id`)',
string: 'UPDATE `post` SET `content` = `user`.`name` FROM `user` WHERE (`post`.`userId` = `user`.`id`)'
},
params: []

@@ -54,7 +112,18 @@ });

Harness.test({
query : post.update({userId: user.id}).from(user).where(post.userId.equals(user.id)),
pg : 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")',
sqlite: 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")',
mysql : 'UPDATE `post` SET `userId` = `user`.`id` FROM `user` WHERE (`post`.`userId` = `user`.`id`)',
query: post.update({
userId: user.id
}).from(user).where(post.userId.equals(user.id)),
pg: {
text : 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")',
string: 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")'
},
sqlite: {
text : 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")',
string: 'UPDATE "post" SET "userId" = "user"."id" FROM "user" WHERE ("post"."userId" = "user"."id")'
},
mysql: {
text : 'UPDATE `post` SET `userId` = `user`.`id` FROM `user` WHERE (`post`.`userId` = `user`.`id`)',
string: 'UPDATE `post` SET `userId` = `user`.`id` FROM `user` WHERE (`post`.`userId` = `user`.`id`)'
},
params: []
});

68

test/dialects/value-expression-tests.js

@@ -9,6 +9,15 @@ 'use strict';

Harness.test({
query : customer.select(customer.name, customer.income.modulo(100)).where(customer.age.plus(5).multiply(customer.age.minus(2)).equals(10)),
pg : 'SELECT "customer"."name", ("customer"."income" % $1) FROM "customer" WHERE ((("customer"."age" + $2) * ("customer"."age" - $3)) = $4)',
sqlite: 'SELECT "customer"."name", ("customer"."income" % $1) FROM "customer" WHERE ((("customer"."age" + $2) * ("customer"."age" - $3)) = $4)',
mysql : 'SELECT `customer`.`name`, (`customer`.`income` % ?) FROM `customer` WHERE (((`customer`.`age` + ?) * (`customer`.`age` - ?)) = ?)',
query: customer.select(customer.name, customer.income.modulo(100)).where(customer.age.plus(5).multiply(customer.age.minus(2)).equals(10)),
pg: {
text : 'SELECT "customer"."name", ("customer"."income" % $1) FROM "customer" WHERE ((("customer"."age" + $2) * ("customer"."age" - $3)) = $4)',
string: 'SELECT "customer"."name", ("customer"."income" % 100) FROM "customer" WHERE ((("customer"."age" + 5) * ("customer"."age" - 2)) = 10)'
},
sqlite: {
text : 'SELECT "customer"."name", ("customer"."income" % $1) FROM "customer" WHERE ((("customer"."age" + $2) * ("customer"."age" - $3)) = $4)',
string: 'SELECT "customer"."name", ("customer"."income" % 100) FROM "customer" WHERE ((("customer"."age" + 5) * ("customer"."age" - 2)) = 10)'
},
mysql: {
text : 'SELECT `customer`.`name`, (`customer`.`income` % ?) FROM `customer` WHERE (((`customer`.`age` + ?) * (`customer`.`age` - ?)) = ?)',
string: 'SELECT `customer`.`name`, (`customer`.`income` % 100) FROM `customer` WHERE (((`customer`.`age` + 5) * (`customer`.`age` - 2)) = 10)'
},
params: [100, 5, 2, 10]

@@ -19,6 +28,15 @@ });

Harness.test({
query : customer.select(customer.name).where(customer.name.like(customer.id.plus('hello'))),
pg : 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + $1))',
sqlite: 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + $1))',
mysql : 'SELECT `customer`.`name` FROM `customer` WHERE (`customer`.`name` LIKE (`customer`.`id` + ?))',
query: customer.select(customer.name).where(customer.name.like(customer.id.plus('hello'))),
pg: {
text : 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + $1))',
string: 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + \'hello\'))'
},
sqlite: {
text : 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + $1))',
string: 'SELECT "customer"."name" FROM "customer" WHERE ("customer"."name" LIKE ("customer"."id" + \'hello\'))'
},
mysql: {
text : 'SELECT `customer`.`name` FROM `customer` WHERE (`customer`.`name` LIKE (`customer`.`id` + ?))',
string: 'SELECT `customer`.`name` FROM `customer` WHERE (`customer`.`name` LIKE (`customer`.`id` + \'hello\'))'
},
params: ['hello']

@@ -30,6 +48,15 @@ });

Harness.test({
query : v.select(v.a.multiply(v.a).divide(2).plus(v.v.multiply(v.t)).equals(v.d)),
pg : 'SELECT (((("variable"."a" * "variable"."a") / $1) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"',
sqlite: 'SELECT (((("variable"."a" * "variable"."a") / $1) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"',
mysql : 'SELECT ((((`variable`.`a` * `variable`.`a`) / ?) + (`variable`.`v` * `variable`.`t`)) = `variable`.`d`) FROM `variable`',
query: v.select(v.a.multiply(v.a).divide(2).plus(v.v.multiply(v.t)).equals(v.d)),
pg: {
text : 'SELECT (((("variable"."a" * "variable"."a") / $1) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"',
string: 'SELECT (((("variable"."a" * "variable"."a") / 2) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"'
},
sqlite: {
text : 'SELECT (((("variable"."a" * "variable"."a") / $1) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"',
string: 'SELECT (((("variable"."a" * "variable"."a") / 2) + ("variable"."v" * "variable"."t")) = "variable"."d") FROM "variable"'
},
mysql: {
text : 'SELECT ((((`variable`.`a` * `variable`.`a`) / ?) + (`variable`.`v` * `variable`.`t`)) = `variable`.`d`) FROM `variable`',
string: 'SELECT ((((`variable`.`a` * `variable`.`a`) / 2) + (`variable`.`v` * `variable`.`t`)) = `variable`.`d`) FROM `variable`'
},
params: [2]

@@ -40,7 +67,16 @@ });

Harness.test({
query : v.select(v.a.multiply(v.a).plus(v.b.multiply(v.b)).equals(v.c.multiply(v.c))),
pg : 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"',
sqlite: 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"',
mysql : 'SELECT (((`variable`.`a` * `variable`.`a`) + (`variable`.`b` * `variable`.`b`)) = (`variable`.`c` * `variable`.`c`)) FROM `variable`',
query: v.select(v.a.multiply(v.a).plus(v.b.multiply(v.b)).equals(v.c.multiply(v.c))),
pg: {
text : 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"',
string: 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"'
},
sqlite: {
text : 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"',
string: 'SELECT ((("variable"."a" * "variable"."a") + ("variable"."b" * "variable"."b")) = ("variable"."c" * "variable"."c")) FROM "variable"'
},
mysql: {
text : 'SELECT (((`variable`.`a` * `variable`.`a`) + (`variable`.`b` * `variable`.`b`)) = (`variable`.`c` * `variable`.`c`)) FROM `variable`',
string: 'SELECT (((`variable`.`a` * `variable`.`a`) + (`variable`.`b` * `variable`.`b`)) = (`variable`.`c` * `variable`.`c`)) FROM `variable`'
},
params: []
});

36

test/dialects/where-clause-tests.js

@@ -7,13 +7,33 @@ 'use strict';

Harness.test({
query : user.where(user.id.isNotNull(), user.name.isNotNull()),
pg : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
mysql : 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))',
sqlite: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
query: user.where(user.id.isNotNull(), user.name.isNotNull()),
pg: {
text : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
string: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
},
mysql: {
text : 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))',
string: 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))'
},
sqlite: {
text : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
string: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
},
params: []
});
Harness.test({
query : user.and(user.id.isNotNull(), user.name.isNotNull()),
pg : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
mysql : 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))',
sqlite: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
query: user.and(user.id.isNotNull(), user.name.isNotNull()),
pg: {
text : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
string: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
},
mysql: {
text : 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))',
string: 'SELECT * FROM `user` WHERE ((`user`.`id` IS NOT NULL) AND (`user`.`name` IS NOT NULL))'
},
sqlite: {
text : 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))',
string: 'SELECT * FROM "user" WHERE (("user"."id" IS NOT NULL) AND ("user"."name" IS NOT NULL))'
},
params: []
});

38

test/index-tests.js

@@ -5,3 +5,3 @@ /* global suite, test */

var sql = require(__dirname + '/../lib');
var sql = require('../lib');

@@ -20,6 +20,6 @@ var user = sql.define({

test('throws before dialect is set', function() {
assert.throws(function() {
var query = sql.select(user.id).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery();
});
test('can create a query using the default dialect', function() {
var query = sql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery();
assert.equal(query.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
assert.equal(query.values[0], 'brian.m.carlson@gmail.com');
});

@@ -66,2 +66,30 @@

});
test('override dialect for toQuery using dialect name', function() {
var Sql = sql.Sql;
var mysql = new Sql('mysql');
var postgres = new Sql('postgres');
var sqlite = new Sql('sqlite');
var sqliteQuery = mysql.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('sqlite');
var postgresQuery = sqlite.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('postgres');
var mysqlQuery = postgres.select(user.id).from(user).where(user.email.equals('brian.m.carlson@gmail.com')).toQuery('mysql');
var values = ['brian.m.carlson@gmail.com'];
assert.equal(sqliteQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
assert.deepEqual(sqliteQuery.values, values);
assert.equal(postgresQuery.text, 'SELECT "user"."id" FROM "user" WHERE ("user"."email" = $1)');
assert.deepEqual(postgresQuery.values, values);
assert.equal(mysqlQuery.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
assert.deepEqual(mysqlQuery.values, values);
});
test('override dialect for toQuery using invalid dialect name', function() {
var query = sql.select(user.id).from(user);
assert.throws(function() {
query.toQuery('invalid');
});
});
});
.travis.yml

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc