sql-query - npm Package Compare versions

Comparing version 0.0.14 to 0.0.15

lib/Remove.js

@@ -16,5 +16,4 @@ var Where = require("./Where");
 		where: function () {
-			var where = [];
 			for (var i = 0; i < arguments.length; i++) {
-				where.push({
+				sql.where.push({
 					t: null,
@@ -24,3 +23,2 @@ w: arguments[i]
 			}
-			sql.where.push(where);
 			return this;
@@ -27,0 +25,0 @@ },

lib/Select.js

@@ -85,3 +85,4 @@ var Where               = require("./Where");
 		where: function () {
-			var where = [];
+			var where = null;
+
 			for (var i = 0; i < arguments.length; i++) {
@@ -92,15 +93,21 @@ if (arguments[i] === null) {
 				if (typeof arguments[i] == "string") {
-					where.push({
+					if (where !== null) {
+						sql.where.push(where);
+					}
+					where = {
 						t: get_table_alias(arguments[i]),
 						w: arguments[i + 1]
-					});
+					};
 					i++;
 				} else {
-					where.push({
+					if (where !== null) {
+						sql.where.push(where);
+					}
+					where = {
 						t: null,
 						w: arguments[i]
-					});
+					};
 				}
 			}
-			if (where.length > 0) {
+			if (where !== null) {
 				sql.where.push(where);
@@ -107,0 +114,0 @@ }

lib/Update.js

@@ -21,5 +21,4 @@ var Set   = require("./Set");
 		where: function () {
-			var where = [];
 			for (var i = 0; i < arguments.length; i++) {
-				where.push({
+				sql.where.push({
 					t: null,
@@ -29,3 +28,2 @@ w: arguments[i]
 			}
-			sql.where.push(where);
 			return this;
@@ -32,0 +30,0 @@ },

lib/Where.js

@@ -6,8 +6,9 @@ exports.build = function (Dialect, where) {
 
-	var query = [];
+	var query = [], subquery;
 
 	for (var i = 0; i < where.length; i++) {
-		var group = buildOrGroup(Dialect, where[i]);
-		if (group !== false) {
-			query.push(group);
+		subquery = buildOrGroup(Dialect, where[i]);
+
+		if (subquery !== false) {
+			query.push(subquery);
 		}
@@ -18,80 +19,99 @@ }
 		return [];
+	} else if (query.length == 1) {
+		return "WHERE " + query[0];
 	}
 
-	return "WHERE (" + query.join(") OR (") + ")";
+	return "WHERE (" + query.join(") AND (") + ")";
 };
 
 function buildOrGroup(Dialect, where) {
-	var query = [];
+	if (where.e) {
+		// EXISTS
+		return [
+			"EXISTS (" +
+			"SELECT * FROM " + Dialect.escapeId(where.e.t) + " " +
+			"WHERE " + Dialect.escapeId(where.e.l[0]) + " = " + Dialect.escapeId(where.e.tl, where.e.l[1]) + " " +
+			"AND " + buildOrGroup(Dialect, [{ t: null, w: where.w }]) +
+			")"
+		];
+	}
 
-	for (var i = 0; i < where.length; i++) {
-		if (where[i].e) {
-			// EXISTS
+	var query = [], op;
+
+	for (var k in where.w) {
+		if (!where.w[k]) {
 			query.push(
-				"EXISTS (" +
-				"SELECT * FROM " + Dialect.escapeId(where[i].e.t) + " " +
-				"WHERE " + Dialect.escapeId(where[i].e.l[0]) + " = " + Dialect.escapeId(where[i].e.tl, where[i].e.l[1]) + " " +
-				"AND " + buildOrGroup(Dialect, [{ t: null, w: where[i].w }]) +
-				")"
+				buildComparisonKey(Dialect, where.t, k) +
+				" IS NULL"
 			);
 			continue;
 		}
-		for (var k in where[i].w) {
-			if (where[i].w[k] === null) {
-				query.push(
-					buildComparisonKey(Dialect, where[i].t, k) +
-					" IS NULL"
-				);
-				continue;
-			}
-			if (typeof where[i].w[k].sql_comparator == "function") {
-				var op = where[i].w[k].sql_comparator();
+		// not is an alias for not_and
+		if ([ "or", "and", "not_or", "not_and", "not" ].indexOf(k) >= 0) {
+			var q, subquery = [];
+			var prefix = (k == "not" || k.indexOf("_") >= 0 ? "NOT " : false);
 
-				switch (op) {
-					case "between":
-						query.push(
-							buildComparisonKey(Dialect, where[i].t, k) +
-							" BETWEEN " +
-							Dialect.escapeVal(where[i].w[k].from) +
-							" AND " +
-							Dialect.escapeVal(where[i].w[k].to)
-						);
-						break;
-					case "like":
-						query.push(
-							buildComparisonKey(Dialect, where[i].t, k) +
-							" LIKE " +
-							Dialect.escapeVal(where[i].w[k].expr)
-						);
-						break;
-					case "eq":
-					case "ne":
-					case "gt":
-					case "gte":
-					case "lt":
-					case "lte":
-						switch (op) {
-							case "eq"  : op = "=";  break;
-							case "ne"  : op = "<>";  break;
-							case "gt"  : op = ">";  break;
-							case "gte" : op = ">="; break;
-							case "lt"  : op = "<";  break;
-							case "lte" : op = "<="; break;
-						}
-						query.push(
-							buildComparisonKey(Dialect, where[i].t, k) +
-							" " + op + " " +
-							Dialect.escapeVal(where[i].w[k].val)
-						);
-						break;
+			op = (k == "not" ? "and" : (k.indexOf("_") >= 0 ? k.substr(4) : k)).toUpperCase();
+
+			for (var j = 0; j < where.w[k].length; j++) {
+				q = buildOrGroup(Dialect, { t: where.t, w: where.w[k][j] });
+				if (q !== false) {
+					subquery.push(q);
 				}
-				continue;
 			}
 
-			query.push(
-				buildComparisonKey(Dialect, where[i].t, k) +
-				(Array.isArray(where[i].w[k]) ? " IN " : " = ") +
-				Dialect.escapeVal(where[i].w[k])
-			);
+			if (subquery.length > 0) {
+				query.push((prefix ? prefix : "") + "((" + subquery.join(") " + op + " (") + "))");
+			}
+			continue;
 		}
+		if (typeof where.w[k].sql_comparator == "function") {
+			op = where.w[k].sql_comparator();
+
+			switch (op) {
+				case "between":
+					query.push(
+						buildComparisonKey(Dialect, where.t, k) +
+						" BETWEEN " +
+						Dialect.escapeVal(where.w[k].from) +
+						" AND " +
+						Dialect.escapeVal(where.w[k].to)
+					);
+					break;
+				case "like":
+					query.push(
+						buildComparisonKey(Dialect, where.t, k) +
+						" LIKE " +
+						Dialect.escapeVal(where.w[k].expr)
+					);
+					break;
+				case "eq":
+				case "ne":
+				case "gt":
+				case "gte":
+				case "lt":
+				case "lte":
+					switch (op) {
+						case "eq"  : op = "=";  break;
+						case "ne"  : op = "<>";  break;
+						case "gt"  : op = ">";  break;
+						case "gte" : op = ">="; break;
+						case "lt"  : op = "<";  break;
+						case "lte" : op = "<="; break;
+					}
+					query.push(
+						buildComparisonKey(Dialect, where.t, k) +
+						" " + op + " " +
+						Dialect.escapeVal(where.w[k].val)
+					);
+					break;
+			}
+			continue;
+		}
+
+		query.push(
+			buildComparisonKey(Dialect, where.t, k) +
+			(Array.isArray(where.w[k]) ? " IN " : " = ") +
+			Dialect.escapeVal(where.w[k])
+		);
 	}
@@ -98,0 +118,0 @@

package.json

@@ -9,3 +9,3 @@ {
   ],
-  "version": "0.0.14",
+  "version": "0.0.15",
   "license": "MIT",
@@ -12,0 +12,0 @@ "repository": {

test/integration/test-remove.js

@@ -11,3 +11,3 @@ var common     = require('../common');
 	common.Remove().from('table1').where({ col: 1 }).build(),
-	"DELETE FROM `table1` WHERE (`col` = 1)"
+	"DELETE FROM `table1` WHERE `col` = 1"
 );
@@ -17,8 +17,8 @@
 	common.Remove().from('table1').where({ col1: 1 }, { col2: 2 }).build(),
-	"DELETE FROM `table1` WHERE (`col1` = 1 AND `col2` = 2)"
+	"DELETE FROM `table1` WHERE (`col1` = 1) AND (`col2` = 2)"
 );
 
 assert.equal(
-	common.Remove().from('table1').where({ col: 1 }).where({ col: 2 }).build(),
-	"DELETE FROM `table1` WHERE (`col` = 1) OR (`col` = 2)"
+	common.Remove().from('table1').where({ or: [{ col: 1 }, { col: 2 }] }).build(),
+	"DELETE FROM `table1` WHERE ((`col` = 1) OR (`col` = 2))"
 );

test/integration/test-update.js

@@ -21,3 +21,3 @@ var common     = require('../common');
 	common.Update().into('table1').set({ col1: 1, col2: 2 }).where({ id: 3 }).build(),
-	"UPDATE `table1` SET `col1` = 1, `col2` = 2 WHERE (`id` = 3)"
+	"UPDATE `table1` SET `col1` = 1, `col2` = 2 WHERE `id` = 3"
 );

test/integration/test-where.js

@@ -16,3 +16,3 @@ var common     = require('../common');
 	common.Select().from('table1').where({ col: 1 }).build(),
-	"SELECT * FROM `table1` WHERE (`col` = 1)"
+	"SELECT * FROM `table1` WHERE `col` = 1"
 );
@@ -22,3 +22,3 @@
 	common.Select().from('table1').where({ col: 'a' }).build(),
-	"SELECT * FROM `table1` WHERE (`col` = 'a')"
+	"SELECT * FROM `table1` WHERE `col` = 'a'"
 );
@@ -28,3 +28,3 @@
 	common.Select().from('table1').where({ col: 'a\'' }).build(),
-	"SELECT * FROM `table1` WHERE (`col` = 'a\\'')"
+	"SELECT * FROM `table1` WHERE `col` = 'a\\''"
 );
@@ -34,3 +34,3 @@
 	common.Select().from('table1').where({ col: [ 1, 2, 3 ] }).build(),
-	"SELECT * FROM `table1` WHERE (`col` IN (1, 2, 3))"
+	"SELECT * FROM `table1` WHERE `col` IN (1, 2, 3)"
 );
@@ -40,3 +40,3 @@
 	common.Select().from('table1').where({ col1: 1, col2: 2 }).build(),
-	"SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2)"
+	"SELECT * FROM `table1` WHERE `col1` = 1 AND `col2` = 2"
 );
@@ -46,3 +46,3 @@
 	common.Select().from('table1').where({ col1: 1 }, { col2: 2 }).build(),
-	"SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2)"
+	"SELECT * FROM `table1` WHERE (`col1` = 1) AND (`col2` = 2)"
 );
@@ -52,3 +52,3 @@
 	common.Select().from('table1').where({ col: 1 }).where({ col: 2 }).build(),
-	"SELECT * FROM `table1` WHERE (`col` = 1) OR (`col` = 2)"
+	"SELECT * FROM `table1` WHERE (`col` = 1) AND (`col` = 2)"
 );
@@ -58,3 +58,3 @@
 	common.Select().from('table1').where({ col1: 1, col2: 2 }).where({ col3: 3 }).build(),
-	"SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2) OR (`col3` = 3)"
+	"SELECT * FROM `table1` WHERE (`col1` = 1 AND `col2` = 2) AND (`col3` = 3)"
 );
@@ -66,3 +66,3 @@
 	               .where('table1', { col: 1 }, 'table2', { col: 2 }).build(),
-	"SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1 AND `t2`.`col` = 2)"
+	"SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`t2`.`col` = 2)"
 );
@@ -74,3 +74,3 @@
 	               .where('table1', { col: 1 }, { col: 2 }).build(),
-	"SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1 AND `col` = 2)"
+	"SELECT * FROM `table1` `t1` JOIN `table2` `t2` ON `t2`.`id` = `t1`.`id` WHERE (`t1`.`col` = 1) AND (`col` = 2)"
 );
@@ -80,3 +80,3 @@
 	common.Select().from('table1').where({ col: common.Query.gt(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` > 1)"
+	"SELECT * FROM `table1` WHERE `col` > 1"
 );
@@ -86,3 +86,3 @@
 	common.Select().from('table1').where({ col: common.Query.gte(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` >= 1)"
+	"SELECT * FROM `table1` WHERE `col` >= 1"
 );
@@ -92,3 +92,3 @@
 	common.Select().from('table1').where({ col: common.Query.lt(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` < 1)"
+	"SELECT * FROM `table1` WHERE `col` < 1"
 );
@@ -98,3 +98,3 @@
 	common.Select().from('table1').where({ col: common.Query.lte(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` <= 1)"
+	"SELECT * FROM `table1` WHERE `col` <= 1"
 );
@@ -104,3 +104,3 @@
 	common.Select().from('table1').where({ col: common.Query.eq(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` = 1)"
+	"SELECT * FROM `table1` WHERE `col` = 1"
 );
@@ -110,3 +110,3 @@
 	common.Select().from('table1').where({ col: common.Query.ne(1) }).build(),
-	"SELECT * FROM `table1` WHERE (`col` <> 1)"
+	"SELECT * FROM `table1` WHERE `col` <> 1"
 );
@@ -116,3 +116,3 @@
 	common.Select().from('table1').where({ col: common.Query.between('a', 'b') }).build(),
-	"SELECT * FROM `table1` WHERE (`col` BETWEEN 'a' AND 'b')"
+	"SELECT * FROM `table1` WHERE `col` BETWEEN 'a' AND 'b'"
 );
@@ -122,3 +122,3 @@
 	common.Select().from('table1').where({ col: common.Query.like('abc') }).build(),
-	"SELECT * FROM `table1` WHERE (`col` LIKE 'abc')"
+	"SELECT * FROM `table1` WHERE `col` LIKE 'abc'"
 );

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

32528

increased by4.99%

Number of package files

31

increased by3.33%

Lines of code

1143

increased by4.57%

No dependency changes