sql - npm Package Compare versions

Comparing version 0.51.0 to 0.52.0

lib/dialect/mssql.js

@@ -266,21 +266,6 @@ // TODO: visitCreate needs to support schemas
 Mssql.prototype.visitQueryHelper=function(actions,targets,filters){
-	/**
-	 *
-	 * @param {Node[]} list
-	 * @param {String} type
-	 * @returns {Object|undefined} {index:number, node:Node}
-	 * @private
-	 */
-	function _findNode(list,type){
-		for (var i= 0, len=list.length; i<len; i++) {
-			var n=list[i];
-			if (n.type==type) return {index:i,node:n};
-		}
-		return undefined
-	}
-
 	function _handleLimitAndOffset(){
-		var limitInfo=_findNode(filters,"LIMIT");
-		var offsetInfo=_findNode(filters,"OFFSET");
-		var orderByInfo=_findNode(filters,"ORDER BY");
+		var limitInfo=Mssql.super_.prototype.findNode.call(this, filters, "LIMIT");
+		var offsetInfo=Mssql.super_.prototype.findNode.call(this, filters, "OFFSET");
+		var orderByInfo=Mssql.super_.prototype.findNode.call(this, filters, "ORDER BY");
 
@@ -302,3 +287,3 @@ // no OFFSET or LIMIT then there's nothing special to do
 	function _processLimit(limitInfo){
-		var selectInfo=_findNode(actions,"SELECT");
+		var selectInfo=Mssql.super_.prototype.findNode.call(this, actions, "SELECT");
 		assert(selectInfo!=undefined,"MS SQL Server requires a SELECT clause when using LIMIT");
@@ -330,2 +315,3 @@ // save the LIMIT node with the SELECT node
 
+  Mssql.super_.prototype.handleDistinct.call(this, actions, filters);
 	_handleLimitAndOffset();
@@ -332,0 +318,0 @@

lib/dialect/postgres.js

@@ -111,2 +111,3 @@ 'use strict';
     case 'TRUNCATE'        : return this.visitTruncate(node);
+    case 'DISTINCT'        : return this.visitDistinct(node);
     case 'ALIAS'           : return this.visitAlias(node);
@@ -182,3 +183,5 @@ case 'ALTER'           : return this.visitAlter(node);
 Postgres.prototype.visitSelect = function(select) {
-  var result = ['SELECT', select.nodes.map(this.visit.bind(this)).join(', ')];
+  var result = ['SELECT']
+  if (select.isDistinct) result.push('DISTINCT');
+  result.push(select.nodes.map(this.visit.bind(this)).join(', '));
   this._selectOrDeleteEndIndex = this.output.length + result.length;
@@ -292,2 +295,7 @@ return result;
 
+Postgres.prototype.visitDistinct = function(truncate) {
+  // Nothing to do here since it's handled in the SELECT clause
+  return [];
+};
+
 Postgres.prototype.visitAlias = function(alias) {
@@ -593,2 +601,3 @@ var result = [this.visit(alias.value) + ' AS ' + this.quote(alias.alias)];
 Postgres.prototype.visitQueryHelper=function(actions,targets,filters){
+	this.handleDistinct(actions, filters)
 	// lazy-man sorting
@@ -911,2 +920,32 @@ var sortedNodes = actions.concat(targets).concat(filters);
 
+/**
+ * Broken out as a separate function so that dialects that derive from this class can still use this functionality.
+ *
+ * @param {Node[]} list
+ * @param {String} type
+ * @returns {Object|undefined} {index:number, node:Node}
+ */
+Postgres.prototype.findNode=function(list,type) {
+	for (var i= 0, len=list.length; i<len; i++) {
+		var n=list[i];
+		if (n.type==type) return {index:i,node:n};
+	}
+	return undefined
+}
+
+/**
+ * pulls the DISTINCT node out of the filters and flags the SELECT node that it should be distinct.
+ * Broken out as a separate function so that dialects that derive from this class can still use this functionality.
+ */
+Postgres.prototype.handleDistinct = function(actions,filters) {
+	var distinctNode = this.findNode(filters,"DISTINCT");
+	//if (!distinctNode) distinctNode = _findNode(targets,"DISTINCT");
+	//if (!distinctNode) distinctNode = _findNode(actions,"DISTINCT");
+	if (!distinctNode) return
+	var selectInfo = this.findNode(actions,"SELECT");
+	if (!selectInfo) return // there should be one by now, I think
+	// mark the SELECT node that it's distinct
+	selectInfo.node.isDistinct = true;
+}
+
 module.exports = Postgres;

lib/node/query.js

@@ -24,2 +24,3 @@ 'use strict';
 var Truncate        = require('./truncate');
+var Distinct        = require('./distinct');
 var Alter           = require('./alter');
@@ -316,2 +317,6 @@ var AddColumn       = require('./addColumn');
 
+  distinct: function() {
+    return this.add(new Distinct());
+  },
+
   alter: function() {
@@ -318,0 +323,0 @@ return this.add(new Alter());

lib/node/select.js

@@ -13,4 +13,6 @@ 'use strict';
     // used when processing LIMIT clauses in MSSQL
-    this.msSQLLimitNode=undefined;
+    this.msSQLLimitNode = undefined;
+    // set to true when a DISTINCT is used on the entire result set
+    this.isDistinct = false;
   }
 });

package.json

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

test/dialects/distinct-tests.js

@@ -47,1 +47,67 @@ 'use strict';
 });
+
+// BELOW HERE TEST DISTINCT ON THE ENTIRE RESULTS SET, NOT JUST ONE COLUMN
+
+Harness.test({
+  query: user.select().distinct(),
+  pg: {
+    text  : 'SELECT DISTINCT "user".* FROM "user"',
+    string: 'SELECT DISTINCT "user".* FROM "user"'
+  },
+  sqlite: {
+    text  : 'SELECT DISTINCT "user".* FROM "user"',
+    string: 'SELECT DISTINCT "user".* FROM "user"'
+  },
+  mysql: {
+    text  : 'SELECT DISTINCT `user`.* FROM `user`',
+    string: 'SELECT DISTINCT `user`.* FROM `user`'
+  },
+  mssql: {
+    text  : 'SELECT DISTINCT [user].* FROM [user]',
+    string: 'SELECT DISTINCT [user].* FROM [user]'
+  },
+  params: []
+});
+
+Harness.test({
+  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`'
+  },
+  mssql: {
+    text  : 'SELECT DISTINCT [user].[id] FROM [user]',
+    string: 'SELECT DISTINCT [user].[id] FROM [user]'
+  },
+  params: []
+});
+
+Harness.test({
+  query: user.select(user.id,user.name).distinct(),
+  pg: {
+    text  : 'SELECT DISTINCT "user"."id", "user"."name" FROM "user"',
+    string: 'SELECT DISTINCT "user"."id", "user"."name" FROM "user"'
+  },
+  sqlite: {
+    text  : 'SELECT DISTINCT "user"."id", "user"."name" FROM "user"',
+    string: 'SELECT DISTINCT "user"."id", "user"."name" FROM "user"'
+  },
+  mysql: {
+    text  : 'SELECT DISTINCT `user`.`id`, `user`.`name` FROM `user`',
+    string: 'SELECT DISTINCT `user`.`id`, `user`.`name` FROM `user`'
+  },
+  mssql: {
+    text  : 'SELECT DISTINCT [user].[id], [user].[name] FROM [user]',
+    string: 'SELECT DISTINCT [user].[id], [user].[name] FROM [user]'
+  },
+  params: []
+});
+

test/dialects/insert-tests.js

@@ -411,2 +411,23 @@ 'use strict';
 
+Harness.test({
+  query: post.insert(post.userId).select(user.id).distinct().from(user),
+  pg: {
+    text  : 'INSERT INTO "post" ("userId") SELECT DISTINCT "user"."id" FROM "user"',
+    string: 'INSERT INTO "post" ("userId") SELECT DISTINCT "user"."id" FROM "user"'
+  },
+  sqlite: {
+    text  : 'INSERT INTO "post" ("userId") SELECT DISTINCT "user"."id" FROM "user"',
+    string: 'INSERT INTO "post" ("userId") SELECT DISTINCT "user"."id" FROM "user"'
+  },
+  mysql: {
+    text  : 'INSERT INTO `post` (`userId`) SELECT DISTINCT `user`.`id` FROM `user`',
+    string: 'INSERT INTO `post` (`userId`) SELECT DISTINCT `user`.`id` FROM `user`'
+  },
+  mssql: {
+    text  : 'INSERT INTO [post] ([userId]) SELECT DISTINCT [user].[id] FROM [user]',
+    string: 'INSERT INTO [post] ([userId]) SELECT DISTINCT [user].[id] FROM [user]'
+  },
+  params: []
+});
+
 // Binary inserts
@@ -413,0 +434,0 @@ Harness.test({

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

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

372661

increased by1.24%

Number of package files

130

increased by0.78%

Lines of code

9761

increased by1.19%

Worsened metrics

Number of low supply chain risk alerts

109

increased by0.93%

No dependency changes