sql - npm Package Compare versions

Comparing version 0.43.2 to 0.44.0

lib/dialect/index.js

@@ -12,2 +12,4 @@ 'use strict';
       return require('./sqlite');
+    case 'mssql':
+      return require('./mssql');
     default:
@@ -14,0 +16,0 @@ throw new Error(dialect + ' is unsupported');

lib/dialect/postgres.js

@@ -170,4 +170,8 @@ 'use strict';
   }
-
-  return q + word.replace(new RegExp(q,'g'),q+q) + q;
+  // handle square brackets specially
+  if (q=='['){
+    return '['+word+']'
+  } else {
+    return q + word.replace(new RegExp(q,'g'),q+q) + q;
+  }
 };
@@ -523,3 +527,2 @@
   // so select/insert/update/delete comes before from comes before where
-  var sortedNodes = [];
   var missingFrom = true;
@@ -564,12 +567,24 @@ var hasFrom     = false;
   }
-  // lazy-man sorting
-  sortedNodes = actions.concat(targets).concat(filters);
-  for(i = 0; i < sortedNodes.length; i++) {
-    var res = this.visit(sortedNodes[i]);
-    this.output = this.output.concat(res);
-  }
-  // implicit 'from'
-  return this.output;
+  return this.visitQueryHelper(actions,targets,filters)
 };
 
+/**
+ * We separate out this part of query building so it can be overridden by other implementations.
+ *
+ * @param {Node[]} actions
+ * @param {Node[]} targets
+ * @param {Node[]} filters
+ * @returns {String[]}
+ */
+Postgres.prototype.visitQueryHelper=function(actions,targets,filters){
+	// lazy-man sorting
+	var sortedNodes = actions.concat(targets).concat(filters);
+	for(var i = 0; i < sortedNodes.length; i++) {
+	  var res = this.visit(sortedNodes[i]);
+	  this.output = this.output.concat(res);
+	}
+	// implicit 'from'
+	return this.output;
+}
+
 Postgres.prototype.visitSubquery = function(queryNode) {
@@ -576,0 +591,0 @@ // create another query builder of the current class to build the subquery

lib/node/orderByValue.js

@@ -11,2 +11,5 @@ 'use strict';
     this.direction = config.direction;
+    // used when processing OFFSET and LIMIT clauses in MSSQL
+    this.msSQLOffsetNode=undefined;
+    this.msSQLLimitNode=undefined;
   }
@@ -13,0 +16,0 @@ });

lib/node/query.js

@@ -183,5 +183,2 @@ 'use strict';
     var args = sliced(arguments);
-    if (o.length == 0) {
-      o = {};
-    }
     // object literal
@@ -188,0 +185,0 @@ if (arguments.length === 1 && !o.toNode && !o.forEach) {

lib/node/select.js

@@ -12,3 +12,5 @@ 'use strict';
     }
-  } 
+    // used when processing LIMIT clauses in MSSQL
+    this.msSQLLimitNode=undefined;
+  }
 });

lib/table.js

@@ -50,2 +50,13 @@ 'use strict';
 
+Table.prototype.clone = function(config) {
+  return Table.define(lodash.extend({
+    schema: this._schema,
+    name: this._name,
+    sql: this.sql,
+    columnWhiteList: !!this.columnWhiteList,
+    snakeToCamel: !!this.snakeToCamel,
+    columns: this.columns
+  }, config || {}));
+};
+
 Table.prototype.createColumn = function(col) {
@@ -139,3 +150,3 @@ if(!(col instanceof Column)) {
   return new LiteralNode(literal);
-}
+};
 
@@ -171,5 +182,5 @@ Table.prototype.count = function(alias) {
   query.alias = alias;
-	query.join = function(other) {
-		return new JoinNode('INNER', this.toNode(), other.toNode(), other);
-	}
+  query.join = function(other) {
+    return new JoinNode('INNER', this.toNode(), other.toNode(), other);
+  };
   return query;
@@ -180,3 +191,8 @@ };
   var query = new Query(this);
-  query.insert.apply(query, arguments);
+  if(arguments[0].length == 0){
+    query.select.call(query, this.star());
+    query.where.apply(query,["1=2"]);
+  } else {
+    query.insert.apply(query, arguments);
+  }
   return query;
@@ -183,0 +199,0 @@ };

package.json

@@ -5,3 +5,3 @@ {
   "description": "sql builder",
-  "version": "0.43.2",
+  "version": "0.44.0",
   "homepage": "https://github.com/brianc/node-sql",
@@ -8,0 +8,0 @@ "repository": {

test/dialects/aggregate-tests.js

@@ -22,2 +22,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT COUNT(*) AS [post_count] FROM [post]',
+    string: 'SELECT COUNT(*) AS [post_count] FROM [post]'
+  },
   params: []
@@ -40,2 +44,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT(*) AS [post_count] FROM [post]',
+    string: 'SELECT COUNT(*) AS [post_count] FROM [post]'
+  },
   params: []
@@ -58,2 +66,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT(*) AS [post_amount] FROM [post]',
+    string: 'SELECT COUNT(*) AS [post_amount] FROM [post]'
+  },
   params: []
@@ -76,2 +88,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]',
+    string: 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]'
+  },
   params: []
@@ -94,2 +110,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]',
+    string: 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]'
+  },
   params: []
@@ -112,2 +132,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]',
+    string: 'SELECT COUNT([post].[content]) AS [content_count] FROM [post]'
+  },
   params: []
@@ -147,2 +171,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MIN([post].[id]) AS [id_min] FROM [post]',
+    string: 'SELECT MIN([post].[id]) AS [id_min] FROM [post]'
+  },
   params: []
@@ -165,2 +193,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MIN([post].[id]) AS [min_id] FROM [post]',
+    string: 'SELECT MIN([post].[id]) AS [min_id] FROM [post]'
+  },
   params: []
@@ -183,2 +215,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MIN([post].[id]) AS [min_id] FROM [post]',
+    string: 'SELECT MIN([post].[id]) AS [min_id] FROM [post]'
+  },
   params: []
@@ -201,2 +237,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MAX([post].[id]) AS [id_max] FROM [post]',
+    string: 'SELECT MAX([post].[id]) AS [id_max] FROM [post]'
+  },
   params: []
@@ -219,2 +259,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MAX([post].[id]) AS [max_id] FROM [post]',
+    string: 'SELECT MAX([post].[id]) AS [max_id] FROM [post]'
+  },
   params: []
@@ -237,2 +281,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT MAX([post].[id]) AS [max_id] FROM [post]',
+    string: 'SELECT MAX([post].[id]) AS [max_id] FROM [post]'
+  },
   params: []
@@ -255,2 +303,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT SUM([post].[id]) AS [id_sum] FROM [post]',
+    string: 'SELECT SUM([post].[id]) AS [id_sum] FROM [post]'
+  },
+  params: []
 });
@@ -272,2 +325,7 @@
   },
+  mssql: {
+    text  : 'SELECT SUM([post].[id]) AS [sum_id] FROM [post]',
+    string: 'SELECT SUM([post].[id]) AS [sum_id] FROM [post]'
+  },
+  params: []
 });
@@ -289,2 +347,7 @@
   },
+  mssql: {
+    text  : 'SELECT SUM([post].[id]) AS [sum_id] FROM [post]',
+    string: 'SELECT SUM([post].[id]) AS [sum_id] FROM [post]'
+  },
+  params: []
 });
@@ -306,2 +369,7 @@
   },
+  mssql: {
+    text  : 'SELECT AVG([post].[id]) AS [id_avg] FROM [post]',
+    string: 'SELECT AVG([post].[id]) AS [id_avg] FROM [post]'
+  },
+  params: []
 });
@@ -323,2 +391,7 @@
   },
+  mssql: {
+    text  : 'SELECT AVG([post].[id]) AS [avg_id] FROM [post]',
+    string: 'SELECT AVG([post].[id]) AS [avg_id] FROM [post]'
+  },
+  params: []
 });
@@ -340,2 +413,7 @@
   },
+  mssql: {
+    text  : 'SELECT AVG([post].[id]) AS [avg_id] FROM [post]',
+    string: 'SELECT AVG([post].[id]) AS [avg_id] FROM [post]'
+  },
+  params: []
 });

test/dialects/alias-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT ([customer].[name] IS NULL) AS [nameIsNull] FROM [customer]',
+    string: 'SELECT ([customer].[name] IS NULL) AS [nameIsNull] FROM [customer]'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    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))'
+  },
   params: [10, 20]
@@ -56,3 +64,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT ([customer].[age] BETWEEN @1 AND @2) AS [ageBetween] FROM [customer]',
+    string: 'SELECT ([customer].[age] BETWEEN 10 AND 20) AS [ageBetween] FROM [customer]'
+  },
   params: [10, 20]
 });

test/dialects/alter-table-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'ALTER TABLE [post] DROP COLUMN [content]',
+    string: 'ALTER TABLE [post] DROP COLUMN [content]'
+  },
   params: []
@@ -39,2 +43,6 @@ });
   },
+  mssql: {
+    text  : 'ALTER TABLE [post] DROP COLUMN [content], [userId]',
+    string: 'ALTER TABLE [post] DROP COLUMN [content], [userId]'
+  },
   params: []
@@ -57,2 +65,6 @@ });
   },
+  mssql: {
+    text  : 'ALTER TABLE [post] DROP COLUMN [content], [userId]',
+    string: 'ALTER TABLE [post] DROP COLUMN [content], [userId]'
+  },
   params: []
@@ -75,2 +87,6 @@ });
   },
+  mssql: {
+    text  : 'EXEC sp_rename [post], [posts]',
+    string: 'EXEC sp_rename [post], [posts]'
+  },
   params: []
@@ -105,2 +121,6 @@ });
   },
+  mssql: {
+    text  : 'ALTER TABLE [group] ADD [id] varchar(100)',
+    string: 'ALTER TABLE [group] ADD [id] varchar(100)'
+  },
   params: []
@@ -123,2 +143,6 @@ });
   },
+  mssql: {
+    text  : 'ALTER TABLE [group] ADD [id] varchar(100), [userId] varchar(100)',
+    string: 'ALTER TABLE [group] ADD [id] varchar(100), [userId] varchar(100)'
+  },
   params: []
@@ -141,2 +165,6 @@ });
   },
+  mssql: {
+    text  : 'ALTER TABLE [group] ADD [id] varchar(100), [userId] varchar(100)',
+    string: 'ALTER TABLE [group] ADD [id] varchar(100), [userId] varchar(100)'
+  },
   params: []
@@ -159,2 +187,8 @@ });
   },
+  mssql: {
+    text  : 'Mssql renaming columns not yet implemented',
+    throws: true
+//    text  : 'EXEC sp_rename [group.userId], [newUserId], \'COLUMN\'',
+//    string: 'EXEC sp_rename [group.userId], [newUserId], \'COLUMN\''
+  },
   params: []
@@ -177,2 +211,8 @@ });
   },
+  mssql: {
+    text  : 'Mssql renaming columns not yet implemented',
+    throws: true
+//    text  : 'EXEC sp_rename [group.userId], [newUserId], \'COLUMN\'',
+//    string: 'EXEC sp_rename [group.userId], [newUserId], \'COLUMN\''
+  },
   params: []
@@ -195,2 +235,8 @@ });
   },
+  mssql: {
+    text  : 'Mssql renaming columns not yet implemented',
+    throws: true
+//    text  : 'EXEC sp_rename [group.userId], [id], \'COLUMN\'',
+//    string: 'EXEC sp_rename [group.userId], [id], \'COLUMN\''
+  },
   params: []
@@ -221,2 +267,8 @@ });
     throws: true
+  },
+  mssql: {
+    text  : 'Mssql renaming columns not yet implemented',
+    throws: true
+//    text  : 'EXEC sp_rename [UserWithSignature.Signature], [sig], \'COLUMN\'',
+//    string: 'EXEC sp_rename [UserWithSignature.Signature], [sig], \'COLUMN\''
   }
@@ -223,0 +275,0 @@ });

test/dialects/binary-clause-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT ([customer].[name] + [customer].[age]) FROM [customer]',
+    string: 'SELECT ([customer].[name] + [customer].[age]) FROM [customer]'
+  },
   params: []
@@ -39,2 +43,6 @@ });
   },
+  mssql: {
+    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]))'
+  },
   params: ['!']
@@ -57,3 +65,7 @@ });
   },
+  mssql: {
+    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]))'
+  },
   params: [': ']
 });

test/dialects/cast-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT CAST([customer].[age] AS int) FROM [customer]',
+    string: 'SELECT CAST([customer].[age] AS int) FROM [customer]'
+  },
   params: []
@@ -39,2 +43,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT CAST([customer].[name] AS varchar(10)) FROM [customer]',
+    string: 'SELECT CAST([customer].[name] AS varchar(10)) FROM [customer]'
+  },
   params: []
@@ -48,12 +56,16 @@ });
     text  : 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"',
-    string: 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"',
+    string: 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"',
-    string: 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"',
+    string: 'SELECT CAST(("customer"."name" + "customer"."age") AS varchar(15)) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT CAST((`customer`.`name` + `customer`.`age`) AS varchar(15)) FROM `customer`',
-    string: 'SELECT CAST((`customer`.`name` + `customer`.`age`) AS varchar(15)) FROM `customer`',
+    string: 'SELECT CAST((`customer`.`name` + `customer`.`age`) AS varchar(15)) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT CAST(([customer].[name] + [customer].[age]) AS varchar(15)) FROM [customer]',
+    string: 'SELECT CAST(([customer].[name] + [customer].[age]) AS varchar(15)) FROM [customer]'
+  },
   params: []
@@ -77,2 +89,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT CAST(CAST([customer].[name] AS varchar(15)) AS varchar(10)) FROM [customer]',
+    string: 'SELECT CAST(CAST([customer].[name] AS varchar(15)) AS varchar(10)) FROM [customer]'
+  },
   params: []
@@ -96,2 +112,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [customer].[name] FROM [customer] WHERE ((CAST([customer].[age] AS int) + @1) = @2)',
+    string: 'SELECT [customer].[name] FROM [customer] WHERE ((CAST([customer].[age] AS int) + 100) = 150)'
+  },
   params: [100, 150]
@@ -115,3 +135,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT CAST([customer].[age] AS int) AS [age_int] FROM [customer]',
+    string: 'SELECT CAST([customer].[age] AS int) AS [age_int] FROM [customer]'
+  },
   params: []
 });

test/dialects/clause-ordering-tests.js

@@ -12,3 +12,3 @@ 'use strict';
     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")',
+    string: 'SELECT "user"."name", "post"."content" FROM "user" INNER JOIN "post" ON ("user"."id" = "post"."userId")'
   },
@@ -23,2 +23,7 @@ sqlite: {
   },
+  mssql: {
+    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: []
 });
@@ -43,2 +48,6 @@
   },
+  mssql: {
+    text  : 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = @1)',
+    string: 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = \'\')'
+  },
   params: ['']
@@ -67,2 +76,6 @@ });
   },
+  mssql: {
+    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] = \'\')'
+  },
   params: ['']
@@ -88,3 +101,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = @1)',
+    string: 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = \'\')'
+  },
   params: ['']
 });

test/dialects/create-table-tests.js

@@ -32,2 +32,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'CREATE TABLE [group] ([id] varchar(100), [user_id] varchar(100))',
+    string: 'CREATE TABLE [group] ([id] varchar(100), [user_id] varchar(100))'
+  },
   params: []
@@ -50,2 +54,6 @@ });
   },
+  mssql: {
+    text  : 'IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'group\') BEGIN CREATE TABLE [group] ([id] varchar(100), [user_id] varchar(100)) END',
+    string: 'IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = \'group\') BEGIN CREATE TABLE [group] ([id] varchar(100), [user_id] varchar(100)) END'
+  },
   params: []
@@ -75,2 +83,6 @@ });
     string: 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=InnoDB'
+  },
+  mssql: {
+    text  : 'CREATE TABLE [user] ([id] varchar(100))',
+    string: 'CREATE TABLE [user] ([id] varchar(100))'
   }
@@ -100,2 +112,6 @@ });
     string: 'CREATE TABLE `user` (`id` varchar(100)) DEFAULT CHARSET=latin1'
+  },
+  mssql: {
+    text  : 'CREATE TABLE [user] ([id] varchar(100))',
+    string: 'CREATE TABLE [user] ([id] varchar(100))'
   }
@@ -126,2 +142,6 @@ });
     string: 'CREATE TABLE `user` (`id` varchar(100)) ENGINE=MyISAM DEFAULT CHARSET=latin1'
+  },
+  mssql: {
+    text  : 'CREATE TABLE [user] ([id] varchar(100))',
+    string: 'CREATE TABLE [user] ([id] varchar(100))'
   }
@@ -150,2 +170,6 @@ });
     string: 'CREATE TABLE `user` (`id` int PRIMARY KEY)'
+  },
+  mssql: {
+    text  : 'CREATE TABLE [user] ([id] int PRIMARY KEY)',
+    string: 'CREATE TABLE [user] ([id] int PRIMARY KEY)'
   }
@@ -152,0 +176,0 @@ });

test/dialects/delete-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'DELETE FROM [post] WHERE ([post].[content] = @1)',
+    string: "DELETE FROM [post] WHERE ([post].[content] = 'hello''s world')"
+  },
   params: ["hello's world"]
@@ -95,2 +99,6 @@ });
   },
+  mssql: {
+    text  : 'DELETE FROM [post] WHERE ([post].[content] = @1)',
+    string: "DELETE FROM [post] WHERE ([post].[content] = '')"
+  },
   params: ['']
@@ -115,2 +123,6 @@ });
   },
+  mssql: {
+    text  : 'DELETE FROM [post] WHERE ([post].[content] = @1)',
+    string: "DELETE FROM [post] WHERE ([post].[content] = '')"
+  },
   params: ['']
@@ -135,3 +147,7 @@ });
   },
+  mssql: {
+    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))"
+  },
   params: ['']
 });

test/dialects/distinct-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT DISTINCT([user].[id]) FROM [user]',
+    string: 'SELECT DISTINCT([user].[id]) FROM [user]'
+  },
   params: []
@@ -38,3 +42,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT(DISTINCT([user].[id])) AS [count] FROM [user]',
+    string: 'SELECT COUNT(DISTINCT([user].[id])) AS [count] FROM [user]'
+  },
   params: []
 });

test/dialects/drop-table-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'DROP TABLE [post]',
+    string: 'DROP TABLE [post]'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    text  : 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = [post]) BEGIN DROP TABLE [post] END',
+    string: 'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = [post]) BEGIN DROP TABLE [post] END'
+  },
   params: []
@@ -40,0 +48,0 @@ });

test/dialects/from-clause-tests.js

@@ -20,2 +20,6 @@ 'use strict';
     string: 'SELECT `user`.* FROM `user` , `post`'
+  },
+  mssql: {
+    text  : 'SELECT [user].* FROM [user] , [post]',
+    string: 'SELECT [user].* FROM [user] , [post]'
   }
@@ -37,2 +41,6 @@ });
     string: 'SELECT `user`.*, `post`.* FROM `user` , `post`'
+  },
+  mssql: {
+    text  : 'SELECT [user].*, [post].* FROM [user] , [post]',
+    string: 'SELECT [user].*, [post].* FROM [user] , [post]'
   }
@@ -39,0 +47,0 @@ });

test/dialects/group-by-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [post].[content] FROM [post] GROUP BY [post].[userId]',
+    string: 'SELECT [post].[content] FROM [post] GROUP BY [post].[userId]'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    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: []
@@ -56,2 +64,6 @@ });
   },
+  mssql: {
+    text  : 'SQL Server does not support array_agg.',
+    throws: true
+  },
   params: []
@@ -74,2 +86,6 @@ });
   },
+  mssql: {
+    text  : 'SQL Server does not support array_agg.',
+    throws: true
+  },
   params: []
@@ -92,3 +108,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/having-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql : {
+    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)'
+  },
   params: [10]
@@ -38,2 +42,6 @@ });
   },
+  mssql : {
+    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)'
+  },
   params: [10, 100]
@@ -56,3 +64,7 @@ });
   },
+  mssql : {
+    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)'
+  },
   params: [10, 100]
 });

test/dialects/insert-tests.js

@@ -412,6 +412,6 @@ 'use strict';
   mysql: {
-    text  : 'INSERT INTO `post` () VALUES ()',
-    string: 'INSERT INTO `post` () VALUES ()'
+    text  : 'SELECT `post`.* FROM `post` WHERE (1=2)',
+    string: 'SELECT `post`.* FROM `post` WHERE (1=2)'
   },
   params: []
 });

test/dialects/join-tests.js

@@ -22,2 +22,6 @@ 'use strict';
   },
+  mssql: {
+    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: []
@@ -40,2 +44,6 @@ });
   },
+  mssql: {
+    text  : '[user] INNER JOIN [post] ON ([user].[id] = [post].[userId])',
+    string: '[user] INNER JOIN [post] ON ([user].[id] = [post].[userId])'
+  },
   params: []
@@ -63,2 +71,6 @@ });
   },
+  mssql: {
+    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: []
@@ -81,2 +93,6 @@ });
   },
+  mssql: {
+    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: []
@@ -104,2 +120,6 @@ });
   },
+  mssql: {
+    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: []
@@ -132,3 +152,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/join-to-tests.js

@@ -53,2 +53,6 @@ 'use strict';
   },
+  mssql: {
+    text  : '[user] INNER JOIN [post] ON ([user].[id] = [post].[ownerId])',
+    string: '[user] INNER JOIN [post] ON ([user].[id] = [post].[ownerId])'
+  },
   params: []
@@ -71,2 +75,6 @@ });
   },
+  mssql: {
+    text  : '[post] INNER JOIN [user] ON ([user].[id] = [post].[ownerId])',
+    string: '[post] INNER JOIN [user] ON ([user].[id] = [post].[ownerId])'
+  },
   params: []
@@ -89,3 +97,7 @@ });
   },
+  mssql: {
+    text  : '[user] INNER JOIN [photo] ON ([user].[id] = [photo].[ownerId])',
+    string: '[user] INNER JOIN [photo] ON ([user].[id] = [photo].[ownerId])'
+  },
   params: []
 });

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

@@ -23,2 +23,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT TOP(1) [user].* FROM [user] ORDER BY [user].[name]',
+    string: 'SELECT TOP(1) [user].* FROM [user] ORDER BY [user].[name]'
+  },
   params: []
@@ -41,2 +45,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY',
+    string: 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY'
+  },
   params: []
@@ -59,2 +67,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS',
+    string: 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS'
+  },
   params: []
@@ -81,3 +93,9 @@ });
   },
+  mssql: {
+    text  : 'Microsoft SQL Server does not support OFFSET without and ORDER BY.',
+    throws: true
+  },
   values: ['John', 'John']
 });
+
+// TODO: Should probably have a test case like the one above but including an ORDER BY clause so the mssql case can be tested

test/dialects/namespace-tests.js

@@ -23,2 +23,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [u].[name] FROM [user] AS [u]',
+    string: 'SELECT [u].[name] FROM [user] AS [u]'
+  },
   params: []
@@ -41,2 +45,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [u].* FROM [user] AS [u]',
+    string: 'SELECT [u].* FROM [user] AS [u]'
+  },
   params: []
@@ -60,2 +68,6 @@ });
   },
+  mssql: {
+    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))'
+  },
   params: [3]
@@ -78,2 +90,6 @@ });
   },
+  mssql: {
+    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: []
@@ -109,3 +125,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT [comment].[text], [comment].[userId] FROM [comment]',
+    string: 'SELECT [comment].[text], [comment].[userId] FROM [comment]'
+  },
   params: []
 });

test/dialects/order-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [post].[content] FROM [post] ORDER BY [post].[content]',
+    string: 'SELECT [post].[content] FROM [post] ORDER BY [post].[content]'
+  },
   params: []
@@ -39,2 +43,6 @@ });
   },
+  mssql: {
+    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: []
@@ -57,2 +65,6 @@ });
   },
+  mssql: {
+    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: []
@@ -75,2 +87,6 @@ });
   },
+  mssql: {
+    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: []
@@ -93,2 +109,6 @@ });
   },
+  mssql: {
+    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: []
@@ -111,2 +131,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL)',
+    string: 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL)'
+  },
   params: []
@@ -129,2 +153,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL) DESC',
+    string: 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL) DESC'
+  },
   params: []
@@ -147,2 +175,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL)',
+    string: 'SELECT ([post].[content] IS NULL) FROM [post] ORDER BY ([post].[content] IS NULL)'
+  },
   params: []
@@ -165,2 +197,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT RTRIM([post].[content]) FROM [post] ORDER BY RTRIM([post].[content])',
+    string: 'SELECT RTRIM([post].[content]) FROM [post] ORDER BY RTRIM([post].[content])'
+  },
   params: []
@@ -183,3 +219,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT RTRIM([post].[content]) FROM [post] ORDER BY RTRIM([post].[content]) DESC',
+    string: 'SELECT RTRIM([post].[content]) FROM [post] ORDER BY RTRIM([post].[content]) DESC'
+  },
   params: []
 });

test/dialects/schema-tests.js

@@ -27,2 +27,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [staging].[user].[id] FROM [staging].[user]',
+    string: 'SELECT [staging].[user].[id] FROM [staging].[user]'
+  },
   params: []
@@ -45,2 +49,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT COUNT([staging].[user].[id]) AS [id_count] FROM [staging].[user]',
+    string: 'SELECT COUNT([staging].[user].[id]) AS [id_count] FROM [staging].[user]'
+  },
   params: []
@@ -63,2 +71,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [staging].[user].[id], [staging].[user].[name] FROM [staging].[user]',
+    string: 'SELECT [staging].[user].[id], [staging].[user].[name] FROM [staging].[user]'
+  },
   params: []
@@ -82,2 +94,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [uws].[name] FROM [staging].[user] AS [uws]',
+    string: 'SELECT [uws].[name] FROM [staging].[user] AS [uws]'
+  },
   params: []
@@ -106,2 +122,6 @@ });
   },
+  mssql: {
+    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: []
@@ -124,3 +144,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/select-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [post].[id], [post].[content] FROM [post]',
+    string: 'SELECT [post].[id], [post].[content] FROM [post]'
+  },
   params: []
@@ -23,0 +27,0 @@ });

test/dialects/shortcut-tests.js

@@ -22,2 +22,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [user].* FROM [user]',
+    string: 'SELECT [user].* FROM [user]'
+  },
   params: []
@@ -40,2 +44,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT * FROM [user] WHERE ([user].[name] = @1)',
+    string: 'SELECT * FROM [user] WHERE ([user].[name] = 3)'
+  },
   params: [3]
@@ -58,2 +66,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT * FROM [user] WHERE (([user].[name] = @1) AND ([user].[id] = @2))',
+    string: 'SELECT * FROM [user] WHERE (([user].[name] = 3) AND ([user].[id] = 1))'
+  },
   params: [3, 1]
@@ -77,2 +89,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].[content] FROM [post]',
+    string: 'SELECT [post].[content] FROM [post]'
+  },
   params: []
@@ -95,2 +111,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].[content] FROM [post] WHERE ([post].[userId] = @1)',
+    string: 'SELECT [post].[content] FROM [post] WHERE ([post].[userId] = 1)'
+  },
   params: [1]
@@ -117,3 +137,7 @@ });
   },
+  mssql: {
+    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))'
+  },
   params: ['', 1]
 });

test/dialects/subquery-tests.js

@@ -26,2 +26,6 @@ 'use strict';
   },
+  mssql: {
+    text  : '([user].[name] IN (SELECT [customer].[name] FROM [customer] WHERE ([user].[name] IN (SELECT [customer].[name] FROM [customer] WHERE ([user].[name] LIKE @1)))))',
+    string: '([user].[name] IN (SELECT [customer].[name] FROM [customer] WHERE ([user].[name] IN (SELECT [customer].[name] FROM [customer] WHERE ([user].[name] LIKE \'%HELLO%\')))))'
+  },
   params: ['%HELLO%']
@@ -44,2 +48,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT * FROM (SELECT * FROM [user])',
+    string: 'SELECT * FROM (SELECT * FROM [user])'
+  },
   params: []
@@ -62,2 +70,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT * FROM (SELECT * FROM [customer]) T1 , (SELECT * FROM [user]) T2',
+    string: 'SELECT * FROM (SELECT * FROM [customer]) T1 , (SELECT * FROM [user]) T2'
+  },
   params: []
@@ -83,2 +95,6 @@ });
   },
+  mssql: {
+    text  : '([customer].[name] BETWEEN (SELECT MIN([customer].[name]) FROM [customer]) AND (SELECT MAX([customer].[name]) FROM [customer]))',
+    string: '([customer].[name] BETWEEN (SELECT MIN([customer].[name]) FROM [customer]) AND (SELECT MAX([customer].[name]) FROM [customer]))'
+  },
   params: []
@@ -101,3 +117,7 @@ });
   },
+  mssql: {
+    text  : '(EXISTS (SELECT * FROM [user] WHERE ([user].[name] = [customer].[name])))',
+    string: '(EXISTS (SELECT * FROM [user] WHERE ([user].[name] = [customer].[name])))'
+  },
   params: []
 });

test/dialects/support.js

@@ -10,3 +10,4 @@ 'use strict';
   sqlite : require('../../lib/dialect/sqlite'),
-  mysql  : require('../../lib/dialect/mysql')
+  mysql  : require('../../lib/dialect/mysql'),
+  mssql  : require('../../lib/dialect/mssql')
 };
@@ -13,0 +14,0 @@

test/dialects/table-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [user].[id] FROM [user]',
+    string: 'SELECT [user].[id] FROM [user]'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[id], [user].[name] FROM [user]',
+    string: 'SELECT [user].[id], [user].[name] FROM [user]'
+  },
   params: []
@@ -56,2 +64,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].* FROM [user]',
+    string: 'SELECT [user].* FROM [user]'
+  },
   params: []
@@ -142,2 +154,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = @1)',
+    string: 'SELECT [user].[id] FROM [user] WHERE ([user].[name] = \'foo\')'
+  },
   params: ['foo']
@@ -160,2 +176,6 @@ });
   },
+  mssql: {
+    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\'))'
+  },
   params: ['foo', 'bar']
@@ -178,2 +198,6 @@ });
   },
+  mssql: {
+    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\'))'
+  },
   params: ['foo', 'bar']
@@ -196,2 +220,6 @@ });
   },
+  mssql: {
+    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\'))'
+  },
   params: ['foo', 'bar']
@@ -214,2 +242,6 @@ });
   },
+  mssql: {
+    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\'))'
+  },
   params: ['foo', 'baz', 'bar']
@@ -232,2 +264,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[id] FROM [user] WHERE ([user].[name] IN (@1, @2))',
+    string: 'SELECT [user].[id] FROM [user] WHERE ([user].[name] IN (\'foo\', \'bar\'))'
+  },
   params: ['foo', 'bar']
@@ -250,2 +286,6 @@ });
   },
+  mssql: {
+    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))'
+  },
   params: ['foo', 'bar', 1]
@@ -268,2 +308,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[id], [user].[name] FROM [user]',
+    string: 'SELECT [user].[id], [user].[name] FROM [user]'
+  },
   params: []
@@ -293,2 +337,6 @@ });
   },
+  mssql: {
+    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)))'
+  },
   params: ['boom', 1, 'bang', 2]
@@ -311,2 +359,6 @@ });
   },
+  mssql: {
+    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: []
@@ -329,2 +381,6 @@ });
   },
+  sqlsever: {
+    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\')'
+  },
   params: ['brian']
@@ -347,2 +403,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[name] FROM [user] WHERE ([user].[name] = @1)',
+    string: 'SELECT [user].[name] FROM [user] WHERE ([user].[name] = \'brian\')'
+  },
   params: ['brian']
@@ -365,2 +425,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT name FROM user WHERE (name <> NULL)',
+    string: 'SELECT name FROM user WHERE (name <> NULL)'
+  },
   params: []
@@ -383,2 +447,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT name,id FROM user WHERE (name <> NULL)',
+    string: 'SELECT name,id FROM user WHERE (name <> NULL)'
+  },
   params: []
@@ -401,2 +469,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT name, id FROM user WHERE (name <> NULL)',
+    string: 'SELECT name, id FROM user WHERE (name <> NULL)'
+  },
   params: []
@@ -419,2 +491,6 @@ });
   },
+  mssql: {
+    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: []
@@ -439,2 +515,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT name FROM user WHERE ([user].[name] = @1)',
+    string: 'SELECT name FROM user WHERE ([user].[name] = \'brian\')'
+  },
   params: ['brian']
@@ -460,2 +540,6 @@ });
   },
+  mssql: {
+    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))'
+  },
   params: ['brian', 1]
@@ -478,2 +562,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [user].[name] AS [quote"quote"tick`tick`] FROM [user]',
+    string: 'SELECT [user].[name] AS [quote"quote"tick`tick`] FROM [user]'
+  },
   params: []
@@ -496,3 +584,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/ternary-clause-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [customer].* FROM [customer] WHERE ([customer].[age] BETWEEN @1 AND @2)',
+    string: 'SELECT [customer].* FROM [customer] WHERE ([customer].[age] BETWEEN 18 AND 25)'
+  },
   params: [18, 25]
@@ -39,3 +43,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/tostring-tests.js

@@ -22,2 +22,6 @@ 'use strict';
   },
+  mssql: {
+    text  : '([post].[content] = @1)',
+    string: '([post].[content] = NULL)'
+  },
   params: [null]
@@ -41,2 +45,6 @@ });
   },
+  mssql: {
+    text  : '([post].[content] = @1)',
+    string: '([post].[content] = 3.14)'
+  },
   params: [3.14]
@@ -60,2 +68,6 @@ });
   },
+  mssql: {
+    text  : '([post].[content] = @1)',
+    string: '([post].[content] = \'hello\'\'\')'
+  },
   params: ['hello\'']
@@ -79,2 +91,6 @@ });
   },
+  mssql: {
+    text  : 'SQL Server does not support arrays.',
+    throws: true
+  },
   params: [1, '2', null]
@@ -98,2 +114,6 @@ });
   },
+  mssql: {
+    text  : '([post].[content] = @1)',
+    string: '([post].[content] = \'2000-01-01T00:00:00.000Z\')'
+  },
   params: [new Date('Sat, 01 Jan 2000 00:00:00 GMT')]
@@ -123,2 +143,6 @@ });
   },
+  mssql: {
+    text  : '([post].[content] = @1)',
+    string: '([post].[content] = \'secretMessage\')'
+  },
   params: [customObject]
@@ -125,0 +149,0 @@ });

test/dialects/unary-clause-tests.js

@@ -21,2 +21,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [customer].* FROM [customer] WHERE ([customer].[age] IS NOT NULL)',
+    string: 'SELECT [customer].* FROM [customer] WHERE ([customer].[age] IS NOT NULL)'
+  },
   params: []
@@ -39,3 +43,7 @@ });
   },
+  mssql: {
+    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: []
 });

test/dialects/update-tests.js

@@ -23,2 +23,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'UPDATE [post] SET [content] = @1',
+    string: 'UPDATE [post] SET [content] = \'test\''
+  },
   params: ['test']
@@ -44,2 +48,6 @@ });
   },
+  mssql: {
+    text  : 'UPDATE [post] SET [content] = @1, [userId] = @2',
+    string: 'UPDATE [post] SET [content] = \'test\', [userId] = 3'
+  },
   params: ['test', 3]
@@ -65,2 +73,6 @@ });
   },
+  mssql: {
+    text  : 'UPDATE [post] SET [content] = @1, [userId] = @2',
+    string: 'UPDATE [post] SET [content] = NULL, [userId] = 3'
+  },
   params: [null, 3]
@@ -86,2 +98,6 @@ });
   },
+  mssql: {
+    text  : 'UPDATE [post] SET [content] = @1, [userId] = @2 WHERE ([post].[content] = @3)',
+    string: 'UPDATE [post] SET [content] = \'test\', [userId] = 3 WHERE ([post].[content] = \'no\')'
+  },
   params: ['test', 3, 'no']
@@ -106,2 +122,6 @@ });
   },
+  mssql: {
+    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: []
@@ -127,2 +147,6 @@ });
   },
+  mssql: {
+    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: []
@@ -129,0 +153,0 @@ });

test/dialects/value-expression-tests.js

@@ -23,2 +23,6 @@ 'use strict';
   },
+  mssql: {
+    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)'
+  },
   params: [100, 5, 2, 10]
@@ -42,2 +46,6 @@ });
   },
+  mssql: {
+    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\'))'
+  },
   params: ['hello']
@@ -62,2 +70,6 @@ });
   },
+  mssql: {
+    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]'
+  },
   params: [2]
@@ -81,2 +93,6 @@ });
   },
+  mssql: {
+    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: []
@@ -83,0 +99,0 @@ });

test/dialects/where-clause-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    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,2 +42,6 @@ });
   },
+  mssql: {
+    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: []
@@ -40,0 +48,0 @@ });

test/index-tests.js

@@ -30,2 +30,6 @@ 'use strict';
 
+  test('stores the mssql dialect', function() {
+    assert.equal(sql.create('mssql').dialectName, 'mssql');
+  });
+
   test('can create a query using the default dialect', function() {
@@ -45,10 +49,10 @@ var query = sql.select(user.id).from(user).where(user.email.equals('[e-mail]')).toQuery();
   test('sql.create creates an instance with a new dialect', function() {
-      var mysql = sql.create('mysql');
-      var query = mysql.select(user.id).from(user).where(user.email.equals('[e-mail]')).toQuery();
-      assert.equal(query.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
-      assert.equal(query.values[0], '[e-mail]');
-
+    var mysql = sql.create('mysql');
+    var query = mysql.select(user.id).from(user).where(user.email.equals('[e-mail]')).toQuery();
+    assert.equal(query.text, 'SELECT `user`.`id` FROM `user` WHERE (`user`.`email` = ?)');
+    assert.equal(query.values[0], '[e-mail]');
   });
 
   test('sql.define for parallel dialects work independently', function() {
+    var mssql = sql.create('mssql');
     var mysql = sql.create('mysql');
@@ -58,2 +62,3 @@ var postgres = sql.create('postgres');
 
+    var mssqlTable = mssql.define({name: 'table', columns: ['column']});
     var mysqlTable = mysql.define({name: 'table', columns: ['column']});
@@ -66,2 +71,3 @@ var postgresTable = postgres.define({name: 'table', columns: ['column']});
     assert.equal(sqliteTable.sql, sqlite);
+    assert.equal(mssqlTable.sql, mssql);
   });
@@ -71,2 +77,3 @@
     var Sql = sql.Sql;
+    var mssql = new Sql('mssql');
     var mysql = new Sql('mysql');
@@ -79,2 +86,3 @@ var postgres = new Sql('postgres');
     assert.equal(sqlite.dialect, require(__dirname + '/../lib/dialect/sqlite'));
+    assert.equal(mssql.dialect, require(__dirname + '/../lib/dialect/mssql'));
   });
@@ -84,2 +92,3 @@
     var Sql = sql.Sql;
+    var mssql = new Sql('mssql');
     var mysql = new Sql('mysql');
@@ -92,2 +101,3 @@ var postgres = new Sql('postgres');
     var mysqlQuery = postgres.select(user.id).from(user).where(user.email.equals('[e-mail]')).toQuery('mysql');
+    var mssqlQuery = mysql.select(user.id).from(user).where(user.email.equals('[e-mail]')).toQuery('mssql');
 
@@ -103,2 +113,5 @@ var values = ['[e-mail]'];
     assert.deepEqual(mysqlQuery.values, values);
+
+    assert.equal(mssqlQuery.text, 'SELECT [user].[id] FROM [user] WHERE ([user].[email] = @1)');
+    assert.deepEqual(mssqlQuery.values, values);
   });
@@ -105,0 +118,0 @@

test/table-tests.js

@@ -190,2 +190,36 @@ 'use strict';
 
+suite('table.clone', function() {
+  test('check if it is a copy, not just a reference', function() {
+    var table = Table.define({ name: 'foo', columns: [] });
+    var copy = table.clone();
+    assert.notEqual(table, copy);
+  });
+
+  test('copy columns', function() {
+    var table = Table.define({ name: 'foo', columns: ['bar'] });
+    var copy = table.clone();
+    assert(copy.get('bar') instanceof Column);
+  });
+
+  test('overwrite config while copying', function() {
+    var table = Table.define({
+      name: 'foo',
+      schema: 'foobar',
+      columns: ['bar'],
+      snakeToCamel: true,
+      columnWhiteList: true
+    });
+
+    var copy = table.clone({
+      schema: 'test',
+      snakeToCamel: false,
+      columnWhiteList: false
+    });
+
+    assert.equal(copy.getSchema(), 'test');
+    assert.equal(copy.snakeToCamel, false);
+    assert.equal(copy.columnWhiteList, false);
+  });
+});
+
 test('dialects', function () {
@@ -204,2 +238,2 @@ var sql = new Sql.Sql('mysql');
   assert.equal(actual, '"foo" INNER JOIN "bar" ON ("bar"."id" = 1)');
-});
+});

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Dynamic require

Supply chain risk

Dynamic require can indicate the package is performing dangerous or unsafe dynamic code execution.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

342469

increased by15.68%

Number of package files

123

increased by0.82%

Lines of code

8996

increased by13.34%

Worsened metrics

Number of low supply chain risk alerts

104

increased by1.96%

No dependency changes